Outlook Automation - Attachment Position

[darbid]

I am working on sending emails with Outlook from an access form.

For any lerners that stumble upon this first post please note that it is not perfrect and could be written better. And that I have one problem with the positioning of the attachment. Otherwise the first part here is somewhere in my form probably under a click event. There are a number of elements that you need for the call.

   Dim strwhoto As String
    Dim strthesubject As String
    Dim strmessage As String
    Dim strattachment As String
    Dim strposition As Integer
    Dim strnameof As String
    
    strwhoto = "myemail@gmx.com"
    strthesubject = "Hello"
    strmessage = "Dear all;" & vbNewLine & vbNewLine & "Here is the attachement." & vbNewLine & vbNewLine & "Testing this works." & vbNewLine
    strattachment = "C:\things to note.txt"
    [COLOR=Red]strposition = Len(strmessage) + 1[/COLOR]
    strnameof = "The Meeting"
    
 Call SendMessage(strwhoto, strthesubject, strmessage, strattachment, strposition, strnameof)

Then the call will go to this below where all the work will be done.

Option Compare Database
Option Explicit

Public Function SendMessage(strTo, strSubject, STRBodyMessage As String, _
Optional AttachmentPath As String, Optional AttachmentPosition As Integer, _
Optional AttachmentName As String, Optional BCC, Optional strBCC)

Dim msgOutlook As Outlook.MailItem
Dim appOutlook As Outlook.Application
Dim expOutlook As Outlook.Explorer
Dim insOutlook As Outlook.Inspector
Dim sigstring As String
Dim Signature As String


On Error GoTo ErrorHandler
   
'Set Application variable using GetObject; error handler falls back to CreateObject if Outlook is not open
   Set appOutlook = GetObject(, "Outlook.Application")
   Set expOutlook = appOutlook.ActiveExplorer
      
   
    Set msgOutlook = appOutlook.CreateItem(olMailItem)
    Set insOutlook = msgOutlook.GetInspector
    
    
        sigstring = "C:\Documents and Settings\" & Environ("username") & _
                "\Application Data\Microsoft\Signatures\mysig.txt"

 
    If Dir(sigstring) <> "" Then
        Signature = GetSIG(sigstring)
    Else
        Signature = ""
    End If

     With msgOutlook
        .Subject = strSubject
        .To = strTo
        .Body = STRBodyMessage & vbNewLine & Signature
        .Attachments.Add AttachmentPath, 1, [COLOR=Red]AttachmentPosition[/COLOR], AttachmentName
        .Display
        '.Send
    End With
    
    insOutlook.Activate
    'expOutlook.Activate
    
    Set msgOutlook = Nothing
    Set appOutlook = Nothing
    Set expOutlook = Nothing
    Set expOutlook = Nothing
    
    
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err = 429 Then
'Outlook is not running; open Outlook with CreateObject
      Set appOutlook = CreateObject("Outlook.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Function

This part is only to read what is in your signature file. Your signatures should be

"C:\Documents and Settings\" & Environ("username") & _
"\Application Data\Microsoft\Signatures\

Function GetSIG(ByVal sFile As String) As String
'Dick Kusleika
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetSIG = ts.readall
    ts.Close
End Function

You will see that for the position of the text I check the message length. This seems to work ok.

The problem is that the Signature starts right after the attachment meaning that the name of the attachement is in the first line of the signature. I think there is a known problem with Access/Outlook 2007 but I am not aware of anything for 2003.

My second problem is getting outlook started when it is not already running, it seems to not start, although when I debug it starts ok. Of course once there is an instance of this running there is no problem.

Could anyone offer some help with these two things.

 

[GaryPanic]

try place a at the end of the coding or at the begining

see what happens
vbNewLine

 

[darbid]

try place a at the end of the coding or at the begining

see what happens
vbNewLine

am not exactly sure what that means, but I changed the order making this first or last. This did not change anything.

.Attachments.Add AttachmentPath, 1, AttachmentPosition, AttachmentName

The interesting thing is that if I add more vbNewlines here between the message and Signature, it moves the attachment down as well.

.Body = STRBodyMessage & vbNewLine & Signature

but still the signature is placed immediately after the attachment.

Another thing could be if we can attach the file NOT to the body of the text but up in the header below the Topic.

 

[darbid]

I pretty much worked it out but you will have to test it with your special situation.

first this bit above is useless. But has nothing to do with my problem

Set expOutlook = appOutlook.ActiveExplorer

As for the Attachement position. The attachment is placed in the body exactly in the position number you choose after all text is in the body. Not only that but it REPLACES that character.

I therefore made some dummy text and made the attachment replace this dummy text. eg

my text
vbnewline
vbnewline
a
vbnewline
vbnewline
mysignature

Then I get the position number of the "a" above and make that the position of the attachment. The attachment will go exactly where the a was and the "a" is not seen. Thus you will have you body nicely formated with some spave before and after your attachment.