0

Trying to send emails using VBA however it keeps sending from my personal email address despite best efforts. Please can someone advise how to send from secondary email address?

Sub Send_Mail()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Send_Mail")
Dim I As Integer

Dim OA As Object
Dim msg As Object

Set OA = CreateObject("outlook.application")
Set OutAccount = OA.Session.Accounts.Item(2)

Dim last_row As Integer
last_row = Application.CountA(sh.Range("A:A"))

For I = 2 To last_row
    Set msg = OA.CreateItem(0)
    msg.SendUsingAccount = OutAccount
    msg.To = sh.Range("B" & I).Value
    msg.cc = sh.Range("D" & I).Value
    msg.Subject = sh.Range("E" & I).Value
    msg.body = sh.Range("F" & I).Value
            
    If sh.Range("G" & I).Value <> "" Then
        msg.attachments.Add sh.Range("G" & I).Value
            
End If
    
msg.send
    
sh.Range("H" & I).Value = "Sent"

Next I

MsgBox "All emails sent successfully"

End Sub

Thank you!

jonca
  • 1
  • 1
  • Possible duplicate of [MS Access / Outlook 2010 - how to choose which account to send email from?](https://stackoverflow.com/questions/17977035/ms-access-outlook-2010-how-to-choose-which-account-to-send-email-from) – niton Nov 18 '20 at 19:35
  • Does this answer your question? [How to send email from a specific Outlook account using Excel?](https://stackoverflow.com/questions/55360867/how-to-send-email-from-a-specific-outlook-account-using-excel) – niton Jun 18 '21 at 23:20

1 Answers1

0

You can use .From to set the email account that you want to send it from.

strFrom = Name & "<" & Email & ">"
msg.From = strFrom
Koop
  • 1
  • 2
  • There is no .From. There is `.SendUsingAccount` and `.SentOnBehalfOfName`. – niton Nov 18 '20 at 19:39
  • I'm using the Microsoft Outlook 16.0 Object Library – Koop Nov 20 '20 at 22:28
  • See https://learn.microsoft.com/en-us/office/vba/api/outlook.mailitem for a list of mailitem properties. https://stackoverflow.com/questions/17977035/ms-access-outlook-2010-how-to-choose-which-account-to-send-email-from https://stackoverflow.com/questions/64264085/using-other-mail-account-by-sending-mail-from-excel-to-outlook – niton Nov 20 '20 at 22:39