Saturday, 14 April 2018

How to send an email from another mailbox in Excel VBA | Use on behalf in VBA to send mail from another account | VBA email automation


If you wish to send a mail from an another email account, you can add the account name to SentOnBehalfOfName property of your mail object.

Note : Sometimes, the mailboxes takes time to respond, so I added a while loop to make sure that its' been added. You may add a counter or time variable to exit from the while loop if your mailbox is taking too long to respond, so you will not end up in a infinite loop.

Example :


Dim OutMail As Object, Cell As Range, OutApp As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = "abx@gmail.com"
        .CC = "abx@gmail.com"
        '.BCC = "abx@gmail.com"
        .Subject = "subject"
        'check errors & timing
        On Error Resume Next
        .SentOnBehalfOfName = "Sendfrom@gmail.com"
        Do While Err.Number <> 0
            Err.Clear
            .SentOnBehalfOfName = MailBody.Range("B3")
        Loop
        On Error GoTo 0
           
        .HTMLBody =  "this is body"
        .Save

    End With