How to send emails from a specific account in Outlook automatically using VBA.
Below is the complete VBA code:
Option Explicit
Sub EmailAccounts()
‘add a reference to the Microsoft Outlook Library in your Excel workbook
Dim OutApp As Outlook.Application
Dim i As Long
Dim erow As Long
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Set OutApp = CreateObject(“Outlook.Application”)
For i = 1 To OutApp.Session.Accounts.Count
Sheet1.Cells(erow, 1) = OutApp.Session.Accounts.Item(i)
Sheet1.Cells(erow, 2) = i
erow = erow + 1
Next i
End Sub
Sub MailFromAnyAccount()
‘Works in Excel 2007 or higher
‘add a reference to the Microsoft Outlook Library in your Excel workbook
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim OutAccount As Outlook.Account
Dim strbody As String
Set OutApp = CreateObject(“Outlook.Application”)
Set OutMail = OutApp.CreateItem(olMailItem)
‘Use the account based on the number
Set OutAccount = OutApp.Session.Accounts.Item(1)
‘Or use the email address
‘Set OutAccount = OutApp.Session.Accounts(“[email protected]”)
strbody = “You can learn the following at exceltrainingvideos.com:” & vbNewLine & vbNewLine & _
“Visual Basic for Applications VBA” & vbNewLine & _
“Pivot Tables” & vbNewLine & _
“Solver” & vbNewLine & _
“Data Analysis & charts” & vbNewLine & _
“More…” & vbNewLine & vbNewLine & _
“Dinesh K Takyar”
On Error Resume Next
With OutMail
.To = “[email protected]”
.CC = “[email protected];[email protected]”
.BCC = “[email protected]”
.Subject = “What do you want to learn today in MS Excel?”
.Body = strbody
.SendUsingAccount = OutAccount
.Display ‘or use .Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Set OutAccount = Nothing
End Sub