Excel VBA Macro for Sending Mails in Blue Prism through Outlook (To, CC, BCC, HtmlBody & Email Attach)


New Member
'Paste this on Visual Basic IDE of Excel Developer Mode (I used MS Excel 2016) then add it through MS Excel VBO as Macro for your Email Automation in Blue Prism using Outlook.
'You are required to create a configuration file where you insert details

Sub SendEmail()
'Sends the outlook email notification
Dim OutApp As Object
Dim OutMail As Object
Dim OutlookOpened As Boolean
'Get or create Outlook object and make sure it exists before continuing
OutlookOpened = False
On Error Resume Next
Set OutApp = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
Set OutApp = Outlook.Application
OutlookOpened = True
End If
On Error GoTo 0

If OutApp Is Nothing Then
MsgBox "Cannot start Outlook.", vbExclamation
Exit Sub
End If
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next

'Created a configuration workbook with worksheet named "Config" then the .Cells (row,column) is where you insert coordinates of the cell and type the mail details on it,
With OutMail
.To = ThisWorkbook.Worksheets("Config").Cells(1, 4).Value
.BCC = ThisWorkbook.Worksheets("Config").Cells(2, 4).Value
.Subject = ThisWorkbook.Worksheets("Config").Cells(3, 4).Value
.HTMLBody = ThisWorkbook.Worksheets("Config").Cells(4, 4).Value
.CC = ThisWorkbook.Worksheets("Config").Cells(5, 4).Value
.Attachments.Add (ThisWorkbook.Worksheets("Config").Cells(1, 5).Value)
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


Last edited: