CODEWITHSUNDEEP
excelvbaoutlook
MonBarks
MonBarks

Reputation: 11

Send Outlook email, from Excel, from specified account

I'm trying to send an email from Outlook with an attached .pdf file via Excel 365.

The routine works up to a point, but I have to select the email account manually from the open email otherwise it sends from the default account.

I am using objMail.SendUsingAccount = "[email protected]" but I still have to manually select the email account (name has been changed for security reasons).

Sub Email_Sheet_Click()

    Dim objOutlook As Object
    Dim objMail As Object
    Dim signature As String
    Dim PDF_FileName As String
    Dim oWb As Workbook
    Set oWb = ActiveWorkbook
         
    'PDF File name
    'Change accordingly....
    PDF_FileName = Range("S12").Value
 
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
      PDF_FileName, Quality:=xlQualityStandard, IncludeDocProperties _
      :=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
 
    Set objOutlook = CreateObject("Outlook.Application")
    Set objMail = objOutlook.CreateItem(0)
    objMail.SendUsingAccount = "[email protected]"
    
    With objMail
        .Display
    End With
    signature = objMail.HTMLbody
    
    With objMail
        .To = ActiveSheet.Range("M5")
        .Cc = ActiveSheet.Range("A3")
        .Subject = "Invoice for Daycare Fees"
        .HTMLbody = "<font face=" & Chr(34) & "Calibri" & Chr(34) & " size=" & Chr(34) & 4 & Chr(34) & ">" & "Hello," & "<br> <br>" & "Invoice attached " & "<br> <br>" & "Regards," & "<br> <br>" & "Playgroup Billing" & "</font>"
        .Attachments.Add PDF_FileName
        .Save
        .Display
    End With

    Set objOutlook = Nothing
    Set objMail = Nothing
End Sub

Upvotes: 1

Views: 290

Answers (1)

PaulS
PaulS

Reputation: 940

objMail.SendUsingAccount expects an Accounts object as value. As seen in the microsoft documentation for MailItem.SendUsingAccount, you can enumerate the accounts collection and sent the email from the correct one - like this:

 Dim oAccount As Outlook.account 
 For Each oAccount In Application.Session.Accounts 
     If oAccount.AccountType = olPop3 Then 
       Set oMail.SendUsingAccount = oAccount 
       objMail.Send 
     End If 
 Next

PS: See here for code that turns an SMTP address into an Account object.

Upvotes: 0

Related Questions