Solved VBA Outlook Add default Signature without showing the mail (1 Viewer)

[Saphirah]

Hello everyone,

i am trying to automate sending an email from Access to Outlook.
For that i created the following code:

Sub SendMail()
    Dim OlApp As Outlook.Application
    Dim ObjMail As Outlook.MailItem

    Set OlApp = Outlook.Application
    Set oMail= OlApp.CreateItem(olMailItem)

    oMail.BodyFormat = olFormatHTML
    oMail.Subject = "Subject goes here"
    oMail.Recipients.Add "Email goes here"

    oMail.HTMLBody = "HTML goes here " & oMail.HTMLBody
End Sub

Now i want to add a signature at the end of the mail and then send the email. The signature can be added using .display

Sub SendMail()

    Dim OlApp As Outlook.Application

    Dim ObjMail As Outlook.MailItem


    Set OlApp = Outlook.Application

    Set oMail= OlApp.CreateItem(olMailItem)


    oMail.BodyFormat = olFormatHTML

    oMail.Subject = "Subject goes here"

    oMail.Recipients.Add "Email goes here"

    oMail.Display
    oMail.HTMLBody = "HTML goes here " & oMail.HTMLBody
    
    oMail.Send
End Sub

With the unintended side effect that the email is opened, and immediately closed. This not only looks annoying, it also takes much longer than just generating and sending the emails.
I want to send these Emails in the background, as i am batching quite a lot of emails and the user should not sit in front of a flashing screen for a minute or so.

There are a lot of code examples online regarding this, but every single one uses .display.

So is there a way to add a signature without displaying the email?
Thank you very much for your help.

 

[Gasman]

I used to split the email, but that method did not handle images in the signature.?

 

[Saphirah]

What do you mean by "splitting" the email?
Images in signature is not important as we are only using text

 

[Gasman]

I took the basic blank email, split it into a header and footer.
Inserted the required html and then put it all back together again.

More there, than you need as I wished to send one email for all a clients transactions, hence the inner loop.

Site thinks it is more than 10K characters. Appears it counts spaces as well.
Code attached instead

 

[arnelgp]

There are a lot of code examples online regarding this, but every single one uses .display.

not everyone.

 

[Saphirah]

I took the basic blank email, split it into a header and footer.
Inserted the required html and then put it all back together again.

More there, than you need as I wished to send one email for all a clients transactions, hence the inner loop.

Site thinks it is more than 10K characters. Appears it counts spaces as well.
Code attached instead

Okay let me work through that One sec

 

[Saphirah]

Okay so i took a part of your code. This works beautifull! One needs to pay attention to the naming of the signature though.
Here is the working code:

Public Function GetSignature() As String
    Dim strAppdata, strTemplatePath, strSigPath, strSignature
    ' Get appdata path
    strAppdata = Environ("Appdata")
  
    ' Set paths
    strTemplatePath = strAppdata & "\Microsoft\Templates"
    strSigPath = strAppdata & "\Microsoft\Signatures\SignatureNameHere.htm"
  
  
    'Get the signature if it exists
    If dir(strSigPath) <> "" Then
        strSignature = GetBoiler(strSigPath)
        GetSignature = Mid(strSignature, InStr(strSignature, "<div class=WordSection1>") + 24) ' 6
    End If
End Function

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
    Set fso = Nothing
    Set ts = Nothing
End Function

 

[Gasman]

I do not need to pay anything.
I gave you a method, use it or not, up to you.
You need to change to suit. I was not going to that for you.

 

[Saphirah]

I do not need to pay anything.
I gave you a method, use it or not, up to you.
You need to change to suit. I was not going to that for you.

Hahaha i wanted to say pay attention ^^ The code is perfect how it is Thank you very much for your help

The "you" was more for everyone who stumbles upon this thread in the future Is there another way to say that? I am not an english native

 

[NauticalGent]

One needs to pay attention...

You nailed it.