Adding a signature to Outlook email

[mikenelena]

My code is stripping out the e-mail signature at the line that adds the e-mail body text. The signature includes a small graphic, so that should be HTML. I've been fooling with this code for hours trying suggestions from many internet threads. Essentially we want to attach all PDF invoice files from a folder named for the recipient. Everything works but the signature, and the VBNewLine spacing. I am not committed to the particular style of this code. It is only what I've cobbled together from multiple online resources. If there is a more efficient way to code this that achieves what we need, I'm happy to use it. Thanks in advance for any help this group can offer!

Private Sub cmdSendInvoices_Click()

Dim appOL As Outlook.Application
Dim MailOL As Object
Dim strBody As String
Dim strPath, strFileName As String
Dim fsFolder As Object
Dim fsFile As Object
Dim Pattern As String
Dim SSignature As String
Dim Adjuster As String

Dim Size As Integer
Size = Me.ctrlListBox.ListCount - 1
ReDim ListBoxContents(0 To Size) As String
Dim i As Integer

For i = 0 To Size
    ListBoxContents(i) = Me.ctrlListBox.ItemData(i)
Next i

For i = 0 To Size

Set appOL = GetObject(, "Outlook.Application")
Set MailOL = appOL.CreateItem(olMailItem)

Adjuster = DLookup("[AdjusterFirst]", "qryEmailFinal", "[Adjuster Full Name] = '" & ListBoxContents(i) & "'")

strBody = Adjuster & "," & vbNewLine & _
"The attached invoice(s) show as outstanding in our system.  Could we trouble you to check the payment status for us when you have a moment?  Please confirm you have received this e-mail."

With MailOL

strPath = "S:\OurPath\" & ListBoxContents(i) & "\"

Pattern = strPath & "*" & ".*"

strFileName = Dir(Pattern)

Do While strFileName <> ""
.Display
.To = DLookup("[Email]", "qryEmailFinal", "[Adjuster Full Name] = '" & ListBoxContents(i) & "'")
.Subject = "Overdue Invoices"
'.BodyFormat = olFormatHTML
.HTMLBody = strBody & vbNewLine & SSignature

.Attachments.Add strPath & strFileName
strFileName = Dir

Loop

End With

Next i

Set appOL = Nothing
Set MailOL = Nothing

End Sub

 

[welshgasman]

Do let us know how it goes.
I do not remember why I thought I had to split it. Perhaps it was because I did not want to display multiple messages all the time?

Would be interesting to know, if only for the odd email.?

 

[mikenelena]

Sitting here waiting for an appointment, I can't give up on this...Here's a recent thread from this board that may also work.

VBA Add signature to Outlook e-mail

 

[Micron]

I'm going to let Gasman carry on with this one seeing as how I mentioned I haven't created emails with signatures. I could only weigh in where the code doesn't look right and offer suggestions on basic troubleshooting, not the least of which is to step through code !
Last but not least would be to consider re-writing your code so that the function doesn't get the same signature multiple times (if that is applicable, which you didn't comment on).

 

[welshgasman]

I'm going to let Gasman carry on with this one seeing as how I mentioned I haven't created emails with signatures. I could only weigh in where the code doesn't look right and offer suggestions on basic troubleshooting, not the least of which is to step through code !
Last but not least would be to consider re-writing your code so that the function doesn't get the same signature multiple times (if that is applicable, which you didn't comment on).

Yes,
I only get it once at the start, split it, then sandwich my email content between the header and footer.?

 

[mikenelena]

I'm going to let Gasman carry on with this one seeing as how I mentioned I haven't created emails with signatures. I could only weigh in where the code doesn't look right and offer suggestions on basic troubleshooting, not the least of which is to step through code !
Last but not least would be to consider re-writing your code so that the function doesn't get the same signature multiple times (if that is applicable, which you didn't comment on).

Hi Micron,

Thanks for the help with this... It will in fact be the same signature in this loop, so I'll definitely be taking your suggestion on that. I think what I'll do this afternoon is try to rewrite the entire procedure, simply, with as few lines as possible. If I can get the signature working, I can then add back in the loops, etc. Might make troubleshooting it a bit easier.

 

[Micron]

If you get it working, just use the function to return the signature to a variable before entering the loop then use that variable in the loop that sends multiple emails.

 

[mikenelena]

Yes,
I only get it once at the start, split it, then sandwich my email content between the header and footer.?

I considered this, but on further reading discovered that newer versions of Outlook will require the signature graphic to accompany the e-mail as an attachment. Unfortunately that will not work for us. Looks like this one will remain an unsolved mystery for now, but certainly not for lack of effort by Micron and welshgasman. Many thanks to you both for your efforts on this!

 

[welshgasman]

Ah, so that is why I sometimes get the graphic in the email also as an attachment?

Well you know where the graphic is located, so you can attach that as normal on each message? if it is a requirement.
I'm sure Outlook just does that itself? I cannot see the people from whom I have emails like that doing that each time?

Good luck anyway.

 

[mikenelena]

Ah, so that is why I sometimes get the graphic in the email also as an attachment?

Well you know where the graphic is located, so you can attach that as normal on each message? if it is a requirement.
I'm sure Outlook just does that itself? I cannot see the people from whom I have emails like that doing that each time?

Good luck anyway.

Yes, I think you are correct - Outlook does it by itself. But it looks tacky, and simply won't work for us. I'll circle back to this issue after some time. Maybe someone will eventually stumble on something that will work.