0

I maintain a mailing list for a community organization. Originally, one e-mail would be sent to all members on our mailing list using Apple Safari. As the list grew, I learned the local ISPs were rejecting mailings sent from me as the large number of addresses were view as spam.

Since I haven't learned Applescript, I decided to modify a e-mail routine I wrote years ago in VBA. This script launches Outlook out of Excel (MS Office 2010/Windows 7) and creates an individual e-mail to each address on the list instead of one e-mail to everyone. Everything is working fine except the signature will not attach.

Any suggestions?

Here's the code (the "Instructions" sheet contains the list of e-mail addresses [following named range "EmailList"], subject line, and body of email)

Public Sub EmailOrg()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim i As Integer
    Dim shtSource As Worksheet
    Dim iRows As Integer

Set shtSource = Sheets("Instructions")

iRows = Range("EmailHead").CurrentRegion.Rows.Count

For i = 1 To iRows - 1

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

  With OutMail
       .To = Range("EmailList").Offset(i, 0).Value    'list of addresses

       .Subject = shtSource.Cells(2, 5)

       .body = shtSource.Cells(6, 5) 

      '.Display        'or use .Send
        .send
  End With

    Set OutMail = Nothing
    Set OutApp = Nothing

Next i

End Sub

So, I hope someone can help. I also would like to retain the formatting of the signature - is there a way to control formatting within the outlook message from VBA?

Art

Dai
  • 141,631
  • 28
  • 261
  • 374
Art Utay
  • 1
  • 1
  • One typo - "EmailHead" should be "EmailList" – Art Utay Dec 07 '15 at 00:03
  • Is `shtSource.Cells(6,5)` meant to get the signature and set it to the message body? Have you used the step-through debugger to see what happens when the `.Body` property is set? – Dai Dec 07 '15 at 00:03
  • Possible duplicate of [How to add default signature in Outlook](http://stackoverflow.com/questions/8994116/how-to-add-default-signature-in-outlook) – niton Dec 07 '15 at 02:54

1 Answers1

0

The step through debugger was of little help - I was able to find some code written by Ron deBruin that resolved the issue. Apparently, MS office creates two files (one in rtf and one in htm format) when a signature is created in MS Outlook. The proper response is to attach one of those files to the body of the e-mail

user5701659
  • 1