Set the FROM Address when sending emails from excel

[damaniam1604]

Hi all.

I wanted to first say thank you to everyone on here who has helped me in the past. I am very grateful.

My issue: I have some VBA code that I run from Excel. It works as desired, except for the email address from which the emails are being sent. I'd like to avoid the SentOnBehalfOf because I want the emails to be in the sent folder of the desired FROM email address.

Sub Macro1()

    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim r As Long

    Set rng = ActiveSheet.UsedRange

    r = 3   ' This # indicates the first row of applicable data
    
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutAccount = OutApp.Session.Accounts.Item(3)

    Do While r <= rng.Rows.Count
        Set OutMail = OutApp.CreateItem(0)
       
       With OutMail
           
            .To = rng.Cells(r, 6).Value
            .Subject = rng.Cells(r, 7).Value
            .htmlbody = rng.Cells(r, 8).Value
            .Attachments.Add (rng.Cells(r, 10).Value)
            
             .Display
            'or
             'Use display to show messages without sending. Use Send to immediately send message
            
            '.send
            
        End With
       
        Set OutMail = Nothing
        r = r + 1
    Loop
    Set OutApp = Nothing
    Set OutAccount = Nothing
    

End Sub

Thanks in advance for your assistance,

Jordan

 

[rollis13]

Have you tried to use SendUsingAccount instead of SentOnBehalfOfName ?

 

[JBates]

With OutMail

.SentOnBehalfOfName = "youremail@gmail.com"