Choose outlook account to send email / VBA-CodePudding

Hope everything is going well. This is the code I have to send emails from an Excel file, but I want to adapt it to be able to choose the outlook account from the emails are sent from ("[email protected]").

Sub SendEmail()
Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Set Mail_Object = CreateObject("Outlook.Application")
For i = 2 To lr
        With Mail_Object.CreateItem(o)
            .Subject = Range("B" & i).Value
            .To = Range("A" & i).Value
            .Body = Range("C" & i).Value
            '.CC = Range("G" & i).Value
            '.Send
            .display 'disable display and enable send to send automatically
    End With
Next i
        MsgBox "E-mail successfully sent", 64
        Application.DisplayAlerts = False
Set Mail_Object = Nothing
End Sub

I've seen different videos but I get a bit confused on the "Set" parts of the code. Can you help me with it please? Thanks!

CodePudding user response：

I think you're after the SentOnBehalfOfName property:

With Mail_Object.CreateItem(o)
    .Subject = Range("B" & i).Value
    .To = Range("A" & i).Value
    .Body = Range("C" & i).Value
    .SentOnBehalfOfName = "[email protected]"
    '.CC = Range("G" & i).Value
    '.Send
    .display 'disable display and enable send to send automatically
End With