INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

include signature with Outlook email through Access VBA

include signature with Outlook email through Access VBA

(OP)
I have a form that allows the user to 'create' and add multiple attachments to an email that is then sent through outlook.

It all works with one exception, the signature is dropped from the email, can anyone tell me what to add and where in order to use the users default email signature in outlook?

CODE -->

'Collect the message Subject line and body from the form fields
    Dim MsgSubject
    MsgSubject = Me.Text9
    Dim MsgBody
    MsgBody = Me.Text3
    
'Create the email...
        Dim olApp As Outlook.Application
        Dim olMail As Outlook.MailItem
        Dim objOutlookAttach As Outlook.Attachment
        
        Set olApp = New Outlook.Application
        Set olMail = olApp.CreateItem(olMailItem)

        With olMail
            .BodyFormat = olFormatHTML
            .To = Me.Text11
            .CC = ""
            .Subject = MsgSubject
            .Body = MsgBody
        
            'Add Attachments
            Dim db As DAO.Database
            Dim rstAttachments As DAO.Recordset
                
            'Set db = CurrentDb()
            'Set rstAttachments = db.OpenRecordset("Select TEMPEmailAttachments.FilePath FROM TEMPEmailAttachments;")
            
            If Me.List5.ListCount > 0 Then
            Dim varvalue
                With Me.List5
                For Each varvalue In .ItemsSelected
                    olMail.Attachments.Add (Me.List5.Column(0, varvalue))
                Next
                End With
            End If
            .Save
            .Display    'This allows you to preview the message before sending it.
        '    .Send      'If you want to send without seeing / editing the messages uncomment .Send
        End With

Set olMail = Nothing
Set objOutlookAttach = Nothing 

RE: include signature with Outlook email through Access VBA

You may want to check this link: Add outlook Signature using VBA when sending email, or this one: Insert Outlook Signature in mail. There are a lot of other examples on Google.

Just one other point: your variables are variants:

CODE

Dim MsgSubject As Variant
MsgSubject = Me.Text9
Dim MsgBody As Variant
MsgBody = Me.Text3 

and consider renaming your controls. Text9, Text3, Text11 and List5 are not very descriptive.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: include signature with Outlook email through Access VBA

(OP)
Thanks for the suggestions, I have nearly got it working, but it is dropping the MsgBody text and only displaying the signature!

CODE -->

'Collect the message Subject line
    Dim MsgSubject As Variant
    Dim MsgBody As Variant
    
    MsgSubject = Me.Text9
    MsgBody = Me.Text3
    
'Create the email...
        Dim OlApp As Outlook.Application
        Dim olMail As Outlook.MailItem
        Dim objOutlookAttach As Outlook.Attachment
        Dim SigString As String
        Dim signature As String
        
        Set OlApp = New Outlook.Application
        Set olMail = OlApp.CreateItem(olMailItem)
        
        With olMail
            .Display    'This is necessary to collect the default signature as the message must not be modified before collecting it.
        End With
               
        'Change only Mysig.htm to the name of your signature
        SigString = Environ("appdata") & _
                    "\Microsoft\Signatures\Test.htm"
    
        If Dir(SigString) <> "" Then
            signature = GetBoiler(SigString)
        Else
            signature = ""
        End If
    
        On Error Resume Next

        With olMail
            .To = Me.Text11
            .CC = ""
            .Subject = MsgSubject
'            .Body = MsgBody & vbNewLine & signature
            .HTMLBody = MsgBody & vbNewLine & vbNewLine & signature
        
            'Add Attachments
            Dim db As DAO.Database
            Dim rstAttachments As DAO.Recordset
                           
            If Me.List5.ListCount > 0 Then
            Dim varvalue
                With Me.List5
                For Each varvalue In .ItemsSelected
                    olMail.Attachments.Add (Me.List5.Column(0, varvalue))
                Next
                End With
            End If
        '    .Save
            .Display    'This allows you to preview the message before sending it, and stops the automation virus warning.
        '    .Send      'If you want to send without seeing / editing the messages uncomment .Send but you will get virus threat warning from outlook
        End With

Set olMail = Nothing
Set objOutlookAttach = Nothing 

And this is the function that collects the signature template

CODE -->

Function GetBoiler(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)
    GetBoiler = ts.readall
    ts.Close
End Function 

RE: include signature with Outlook email through Access VBA

Instead of (what you used to have and still have):

Dim MsgSubject As Variant
Dim MsgBody As Variant

I would use
Dim MsgSubject As String
Dim MsgBody As String

because that's all you need.

Quote:

but it is dropping the MsgBody text and only displaying the signature!

So this line:
.HTMLBody = MsgBody & vbNewLine & vbNewLine & signature
ignores (drops) MsgBody?

So what do you get if you do this:

CODE

Debug.Print MsgBody 
.HTMLBody = MsgBody & vbNewLine & vbNewLine & signature
Debug.Print .HTMLBody 

This is just a guess on my part, but I suspect your MsgBody is NOT formatted as an HTML text.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: include signature with Outlook email through Access VBA

(OP)
Quite right, needed to ensure that the text was formatted as HMTL.

That is brilliant, thanks

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close