VBA Email Signature Code Proof

Question

I'm fairly new to VBA and I'm having trouble writing a macro that creates an email with my signature already attached. Can someone let me know what exactly is wrong with my code and how I can fix it?

Sub SendFIleAsAttachment()

    Dim OLApp As Outlook.Application
    Dim OLMail As Object
    signature As String

    Set OLApp = New Outlook.Application
    Set OLMail = OLApp.CreateItem(0)
    OLApp.Session.Logon

    With OLMail
    .Display
    End With
        signature = OMail.Body
    With OMail
    .To = "email"
    .CC = "email"
    .BCC = ""
    .Subject = "New Copy Proofing Request"
    .Body = "body text. Thanks!" & vbNewLine & signature
    .Attachments.Add ActiveWorkbook.FullName
    End With
    Set OLMail = Nothing
    Set OLApp = Nothing
End Sub

I may be wrong, but I think Microsoft have done a lot over the years to make this difficult. Using the VBE inspector to examine OLMail (once the typo and the declaration are fixed as @Vityata suggests) I see that the `.Body` (even though the mail contains a signature) is `<>` - which is null I guess as it errors when attempting to load it to a string. — CLR, Jul 10 '18 at 16:06
This might be some kind of permission issue though, I'm on a corporate set-up. You may get different results! — CLR, Jul 10 '18 at 16:08
@CLR - strange, what is `<>`? My code below works quite ok actually. — Vityata, Jul 10 '18 at 16:10
@Vityata I think it's the VBE's attempt at representing a null object (`Nothing`). Or a null of some kind. I think I read somewhere that there's a setting somewhere in GP or similar can prevent certain things being 'visible' to VBA and perhaps this is one. I had to get around it once by reading the .rtf file from %APPDATA%\Microsoft\Signatures but there's no way to detect which is the default signature if more than one exists. — CLR, Jul 11 '18 at 08:52

score 3 · Answer 1 · answered Jul 10 '18 at 15:52

You are misspelling OLMail and OMail and you are missing the word Dim once in your code. To fix this kind of grammer mistakes, follow the good practice inserting Option Explicit on the top of your Module/Worksheet - What do Option Strict and Option Explicit do?

Sub SendFIleAsAttachment()

    Dim OLApp As Outlook.Application
    Dim OLMail As Object
    Dim signature As String

    Set OLApp = New Outlook.Application
    Set OLMail = OLApp.CreateItem(0)

    With OLMail
        .Display
    End With
    signature = OLMail.Body
    With OLMail
        .To = "email"
        .CC = "email"
        .BCC = ""
        .Subject = "New Copy Proofing Request"
        .Body = "body text. Thanks!" & vbNewLine & signature
    End With

End Sub

VBA Email Signature Code Proof

1 Answers1