Add signature to mass email and change the from account

Question

I can't get the signature onto the email and change the from account.

With this code the signature is inserted but it doesn't keep the formatting.

I tried another one that copied it from a Word document but it never worked.

Sub Send_Mails()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
Dim I As Integer
    
Dim OA As Object
Dim msg As Object
Dim OutAccount As Outlook.Account
    
Set OA = CreateObject("outlook.application")
    
Dim last_row As Integer
last_row = Application.CountA(sh.Range("A:A"))
    
Set msg = OA.CreateItem(0)
    
With msg
    .Display
End With
Signature = msg.Body
    
For I = 2 To last_row
    
    'msg.Display
    
    msg.To = sh.Range("A" & I).Value
    msg.cc = sh.Range("B" & I).Value
    msg.Subject = sh.Range("C" & I).Value
    msg.Body = sh.Range("D" & I).Value & Signature
         
    If sh.Range("E" & I).Value <> "" Then
        msg.attachments.Add sh.Range("E" & I).Value
    End If
    
    'msg.send
    
    sh.Range("F" & I).Value = "Sent"
    
Next I
    
MsgBox "All the mails have been sent successfully"
    
End Sub

So you are trying to send out mass emails and have it look like it's coming from someone besides you? Why would you need to do that? — braX, Dec 04 '21 at 22:28
Because we have a general account ex. Info@company.com and we send out all the official communications from that account :) — MWanderlust, Dec 05 '21 at 01:00
Take a look at [Send email from another address using VBA](https://www.slipstick.com/developer/code-samples/send-email-address-vba) and [Insert Outlook Signature in mail](https://www.rondebruin.nl/win/s1/outlook/signature.htm). — Elio Fernandes, Dec 05 '21 at 11:58
Use `.HTMLBody` https://stackoverflow.com/a/12990825/1571407. — niton, Dec 05 '21 at 20:27
@ElioFernandes thank you so much! the first part worked perfectly sendonbehalfof :) — MWanderlust, Dec 06 '21 at 11:48
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. — Community, Dec 10 '21 at 05:07
@community should I edit it even though I got the answers to both questions ? I was able to change the from account using the send on behalf of and add the signature using the HTML.body property — MWanderlust, Dec 11 '21 at 07:01

score 0 · Answer 1 · answered Dec 06 '21 at 11:48

The signature doesn't preserve the original formatting because a plain-text property was used in the code.

Signature = msg.Body

Instead, you need to use the HTMLBody property which returns an HTML markup with all formatting set up. Also you may consider using the Word object model (see the WordEditor property of the Inspector class).

To set up the From account correctly to the one which is set up in an Outlook profile you can use the SendUsingAccount property which returns or sets an Account object that represents the account under which the MailItem is to be sent. For example:

Sub SendUsingAccount() 
 Dim oAccount As Outlook.account
 For Each oAccount In Application.Session.Accounts
   If oAccount.AccountType = olPop3 Then 
     Dim oMail As Outlook.MailItem 
     Set oMail = Application.CreateItem(olMailItem) 
     oMail.Subject = "Sent using POP3 Account" 
     oMail.Recipients.Add ("someone@example.com") 
     oMail.Recipients.ResolveAll 
     Set oMail.SendUsingAccount = oAccount 
     oMail.Send 
   End If 
 Next 
End Sub

Also if you have configured sufficient privileges on the Exchange server side you may consider using the SentOnBehalfOfName property which returns a string indicating the display name for the intended sender of the mail message.

Add signature to mass email and change the from account

1 Answers1