Create and send email in Outlook using script

[jreddy]

Hello everybody,

I'm currently trying to create and send an email with attachements as a part of an automated process.

The process looks as follows:

1. Receive an email with a specific subject and trigger the BLue Prism process
2. Get files from the URL in the email (Excel-files)
3. Do some operations to those files
4. Send newly created Excel files to specified recipients

I tried creating a VBO for Outlook and automate the UI, but Blue Prism is quite bad at distinguishing buttons in Outlook. So I thought there might be some programmatic way to do it. I found this VBA script on Stack Overflow, it seems to be doing exactly what I want:

Sub SendMessage(DisplayMsg As Boolean, Optional AttachmentPath)
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment

    Set objOutlook = CreateObject("Outlook.Application")
    Set objOutlookMsg  = objOutlook.CreateItem(olMailItem)

    With objOutlookMsg
        Set objOutlookRecip = .Recipients.Add("Nancy Davolio")
        objOutlookRecip.Type = olTo
        ' Set the Subject, Body, and Importance of the message.
        .Subject = "This is an Automation test with Microsoft Outlook"
        .Body = "This is the body of the message." &vbCrLf & vbCrLf
        .Importance = olImportanceHigh  'High importance

        If Not IsMissing(AttachmentPath) Then
            Set objOutlookAttach = .Attachments.Add(AttachmentPath)
        End If

        For Each ObjOutlookRecip In .Recipients
            objOutlookRecip.Resolve
        Next

        .Save
        .Send
    End With
    Set objOutlook = Nothing
End Sub

How can I adjust this code to use in a code stage in Blue Prism? Is it worth it or is there a better way to accomplish this?

Thanks in advance for your help!

 

[VJR]

Hi jreddy,

Is there something specific you would like to do with the Code stage such that you are not making use of the ready made available VBOs for email - they are MapiEx (for Outlook) and POP3/SMTP?

Keep in mind of the limitations of MapiEx

• Works only in plain text e-mails and cannot use HTML or Rich Text e-mail formats as of now
• Does not support 64-bit Outlook.

 

[jreddy]

Hi VJR,

we use 64-bit Outlook, thus MapiEx is not an option.

I considered POP3/SMTP VBO, actually that would be the best solution for me. I'm currently trying to clarify with our IT department the info needed to configure POP3/SMTP (like server addresses and port), but it really takes too long. Besides, I don't know whether our corporate security policy will allow me to use a robot to directly connect with those servers.

That's why i decided to try it with a script.

 

[VJR]

Hi VJR,

we use 64-bit Outlook, thus MapiEx is not an option.

I considered POP3/SMTP VBO, actually that would be the best solution for me. I'm currently trying to clarify with our IT department the info needed to configure POP3/SMTP (like server addresses and port), but it really takes too long. Besides, I don't know whether our corporate security policy will allow me to use a robot to directly connect with those servers.

That's why i decided to try it with a script.

Okay, so have you done rest of the bit like steps 1, 2 and 3 and now only step 4 of sending an email with attachments is remaining for which you are looking to incorporate the above code?

 

[jreddy]

Yes, the other steps are done, now I have to figure out how to send an email without messing with UI automation.

 

[VJR]

Hi jreddy,

Okay, if you see that as the right code for you then below is after removing the errors.
I have removed the errors and accommodated it for Blue Prism Code stage, but I haven't run it since I can't use Outlook.
I have commented out 'IsMissing' since that is not available in the above code and you can modify it as per your requirement.

'Add Microsoft.VisualBasic under Namespace Imports section of Initialise tab
    Dim objOutlook As Object 'Outlook.Application
    Dim objOutlookMsg As Object 'Outlook.MailItem
    Dim objOutlookRecip As Object 'Outlook.Recipient
    Dim objOutlookAttach As Object 'Outlook.Attachment
    Dim AttachmentPath as String

     objOutlook = CreateObject("Outlook.Application")
     objOutlookMsg  = objOutlook.CreateItem(0) '0 for olMailItem

    With objOutlookMsg
        objOutlookRecip = .Recipients.Add("Nancy Davolio")
        objOutlookRecip.Type = 1 'for olTo
        ' Set the Subject, Body, and Importance of the message.
        .Subject = "This is an Automation test with Microsoft Outlook"
        .Body = "This is the body of the message." '& vbCrLf & vbCrLf
        .Importance = 2   'olImportanceHigh  'High importance

        'If Not IsMissing(AttachmentPath) Then
            objOutlookAttach = .Attachments.Add(AttachmentPath)
        'End If

        For Each ObjOutlookRecip In .Recipients
            objOutlookRecip.Resolve
        Next

        .Save
        .Send
    End With
    objOutlook = Nothing

For testing purposes, comment out .Send and after .Save use .Display which will popup the Email for viewing.

 

[jreddy]

Hi VJR,

thanks for the code, it works great! I had some troubles in the beginning, but that was because I forgot to import a namespace

I'm quite new to VBA as you can see, and trying to add a For Each loop to the above code, in order to loop through the collection of file paths and attach several files. I tried looking it up in internet, but have not found a solution so far. Maybe you know how to implement it?

 

[VJR]

Hi jreddy,

Yes you had to add the namespace mentioned in the first line of the above code.

For attachment related code you can get it from any of the actions that uses a collection and then tweak it- for eg; excel vbo, collection manipulation vbo. Collections are data tables for the code stage

- Have a collection called as Attachments in the diagram
Add a column to it called as Path
This will be the collection where all your files paths are stored.

- Add an Input parameter to the code stage called as Attachments of type Collection

- The below code goes in the Code stage in the area where you want to add the attachments

Dim FullFilePath as String
For Each row as DataRow in Attachments.Rows
       FullFilePath = row.Item("Path")
       objOutlookAttach = .Attachments.Add(FullFilePath)
Next

- Add the System.Data namespace

 

[sirichandana50]

Hi,

I am using the above code, but I get the following error.could someone please help me with this. It is with .send, when I use .display the code runs perfectly.
"Internal : Could not execute code stage because exception is thrown by code stage: Operation aborted (Exception from HRESULT: 0x80004004 (E_ABORT))"

Thank you.

 

[Vikas]

We can make a changes to MAPIx VBO as below and send email accordingly:

For High Priority/Importance update the below line in the code
,MAPIMessage.Priority.IMPORTANCE_HIGH at below location :

Using msg As New BPMAPIMessage()
If Not msg.Create(mapi,MAPIMessage.Priority.IMPORTANCE_HIGH) Then Throw New MAPIException( _
"Failed to create message")

For low priority /Importance change HIGH to LOW in above code.

Note: This is hard coded solution.

 

[jamunarani]

Hi jreddy,

Yes you had to add the namespace mentioned in the first line of the above code.

For attachment related code you can get it from any of the actions that uses a collection and then tweak it- for eg; excel vbo, collection manipulation vbo. Collections are data tables for the code stage

- Have a collection called as Attachments in the diagram
Add a column to it called as Path
This will be the collection where all your files paths are stored.

- Add an Input parameter to the code stage called as Attachments of type Collection

- The below code goes in the Code stage in the area where you want to add the attachments

Dim FullFilePath as String
For Each row as DataRow in Attachments.Rows
       FullFilePath = row.Item("Path")
       objOutlookAttach = .Attachments.Add(FullFilePath)
Next

- Add the System.Data namespace

 

[jamunarani]

Hi,

i have tried to use the above code for sending email, i am getting the error

Page Stage Name Type Action Description Repairable Action 1Generate_Html_BodyErrorValidateCompiler error at line 8: 'CreateObject' is not declared. It may be inaccessible due to its protection level.No

Kindly help me on this

'Add Microsoft.VisualBasic under Namespace Imports section of Initialise tab
Dim objOutlook As Object 'Outlook.Application
Dim objOutlookMsg As Object 'Outlook.MailItem
Dim objOutlookRecip As Object 'Outlook.Recipient
Dim objOutlookAttach As Object 'Outlook.Attachment
Dim AttachmentPath as String

objOutlook = CreateObject("Outlook.Application")
objOutlookMsg = objOutlook.CreateItem(0) '0 for olMailItem

With objOutlookMsg
objOutlookRecip = .Recipients.Add("Nancy Davolio")
objOutlookRecip.Type = 1 'for olTo
' Set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft Outlook"
.Body = "This is the body of the message." '& vbCrLf & vbCrLf
.Importance = 2 'olImportanceHigh 'High importance

'If Not IsMissing(AttachmentPath) Then
objOutlookAttach = .Attachments.Add(AttachmentPath)
'End If

For Each ObjOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

.Save
.Send
End With
objOutlook = Nothing