hi all,
i’m currently trying to send an email using the outlook activity but the issue that i’m facing is that uipath bypasses my macro settings in outlook application, i do have a macro that runs whenever an email is being sent. however, the macro works when a new email is being sent manually but never works when the robot is sending the email…
any ideas folks?
Hi @kamal_hamad,
Before sending your email with UiPath, you could add an Invoke VBA activity https://docs.uipath.com/activities/docs/invoke-vba.
This new activity would invoke the macro you are speaking about
Hope this helps
Best regards,
Marius
@Marius_Puscasu i get what you’re talking about, actually the invoke vba thing works inside excel scope which i’m not using.
i have the macros set and working inside outlook application itself and what my macros do is they check whenever i try to send an email during non-working hours e.g after 5pm it then delays the email for the next day 8AM, the same way i’m trying to do with the emails sent from my bot so it doesn’t email the business users during night time as i 'm planning to run some unattended processes at midnight
do you get it?
Then, please use the FileName activity https://docs.uipath.com/activities/docs/start-process
Within the FileName filed you should add the absolute path of your outlook macro
Hope this helps
Best regards,
Marius
Option Explicit
Sub Send_Email()
Dim wsData As Worksheet
Dim wsEmail As Worksheet
Dim tbl As ListObject
Dim rw As ListRow
Dim OutApp As Object
Dim OutMail As Object
Dim EmailBody As String
Dim ToAddr As String
Dim CcAddr As String
Dim colEmailStatus As Long
Dim colVetter As Long
Dim colDeclarer As Long
Set wsData = ThisWorkbook.Sheets("Sheet1")
Set wsEmail = ThisWorkbook.Sheets("1stEmail")
Set tbl = wsData.ListObjects("Table1")
' ?? Get dynamic column numbers
colEmailStatus = GetColumnIndex(tbl, "Emailstatus")
colVetter = GetColumnIndex(tbl, "Vetter_Email")
colDeclarer = GetColumnIndex(tbl, "Declarer_Email")
If colEmailStatus = 0 Or colVetter = 0 Or colDeclarer = 0 Then
MsgBox "Required column not found in Table1", vbCritical
Exit Sub
End If
Set OutApp = CreateObject("Outlook.Application")
For Each rw In tbl.ListRows
' ?? Check Emailstatus dynamically
If Trim(rw.Range.Cells(1, colEmailStatus).Value) = "" Then
' ?? Get emails dynamically
ToAddr = rw.Range.Cells(1, colVetter).Value
CcAddr = rw.Range.Cells(1, colDeclarer).Value
' ?? Fill table dynamically
FillEmailTable wsEmail, rw
' ?? Build body (pass current row)
EmailBody = BuildEmailBody(wsEmail, rw)
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = ToAddr
.CC = CcAddr
.Subject = "Initial Email: Vetted Email for Property declaration"
.HTMLBody = EmailBody
.Display 'Change to .Send for auto send
End With
' ?? Mark Sent dynamically
rw.Range.Cells(1, colEmailStatus).Value = "Sent"
End If
Next rw
MsgBox "Emails Process Completed!", vbInformation
End Sub
Function BuildEmailBody(ws As Worksheet, rw As ListRow) As String
Dim html As String
Dim i As Long, j As Long
Dim lastRow As Long
Dim headerRow As Long
Dim dataRow As Long
Dim lastCol As Long
Dim headerCell As Range
Dim tbl As ListObject
Dim col As ListColumn
Dim insertDone As Boolean
Dim bulletChar As String
Dim dataText As String
Set tbl = rw.Parent
html = "<html><body style='font-family:Calibri;font-size:11pt;'>"
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
' ?? Find header row dynamically (using first table header name)
Set headerCell = ws.Cells.Find( _
What:=tbl.HeaderRowRange.Cells(1, 1).Value, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Not headerCell Is Nothing Then
headerRow = headerCell.Row
dataRow = headerRow + 1
lastCol = ws.Cells(headerRow, ws.Columns.Count).End(xlToLeft).Column
End If
insertDone = False
For i = 1 To lastRow
' Stop normal text before table
If i = headerRow Then Exit For
If Trim(ws.Cells(i, 1).Value) = "" Then
html = html & "<br>"
Else
html = html & ws.Cells(i, 1).Value & "<br>"
' ?? Insert dynamic FollowingAdress list
If InStr(1, ws.Cells(i, 1).Value, _
"with your vetting officer.", vbTextCompare) > 0 _
And insertDone = False Then
bulletChar = "a"
For Each col In tbl.ListColumns
If LCase(col.Name) Like "followingadress*" Then
dataText = Trim(rw.Range.Cells(1, col.Index).Value)
If dataText <> "" Then
html = html & bulletChar & ". " & dataText & "<br>"
bulletChar = Chr(Asc(bulletChar) + 1)
End If
End If
Next col
html = html & "<br>"
insertDone = True
End If
End If
Next i
' ?? Now build full HTML table dynamically
If headerRow > 0 Then
html = html & "<table border='1' cellpadding='5' cellspacing='0' style='border-collapse:collapse;'>"
' Header row
html = html & "<tr style='background-color:#f2f2f2;font-weight:bold;'>"
For j = 1 To lastCol
html = html & "<td>" & ws.Cells(headerRow, j).Value & "</td>"
Next j
html = html & "</tr>"
' Data row
html = html & "<tr>"
For j = 1 To lastCol
html = html & "<td>" & ws.Cells(dataRow, j).Value & "</td>"
Next j
html = html & "</tr>"
html = html & "</table><br>"
End If
' ?? Add remaining content after table
For i = dataRow + 1 To lastRow
If Trim(ws.Cells(i, 1).Value) = "" Then
html = html & "<br>"
Else
html = html & ws.Cells(i, 1).Value & "<br>"
End If
Next i
html = html & "</body></html>"
BuildEmailBody = html
End Function
Function BuildEmailBody123(ws As Worksheet, rw As ListRow) As String
Dim html As String
Dim i As Long
Dim lastRow As Long
Dim insertDone As Boolean
Dim bulletChar As String
Dim dataText As String
Dim tbl As ListObject
Dim col As ListColumn
html = "<html><body style='font-family:Calibri;font-size:11pt;'>"
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
insertDone = False
Set tbl = rw.Parent
For i = 1 To lastRow
If Trim(ws.Cells(i, 1).Value) = "" Then
html = html & "<br>"
Else
html = html & ws.Cells(i, 1).Value & "<br>"
' ?? Detect trigger sentence
If InStr(1, ws.Cells(i, 1).Value, _
"with your vetting officer.", vbTextCompare) > 0 _
And insertDone = False Then
bulletChar = "a"
' ?? Loop all columns that start with FollowingAdress
For Each col In tbl.ListColumns
If LCase(col.Name) Like "followingadress*" Then
dataText = Trim(rw.Range.Cells(1, col.Index).Value)
If dataText <> "" Then
html = html & bulletChar & ". " & dataText & "<br>"
bulletChar = Chr(Asc(bulletChar) + 1)
End If
End If
Next col
html = html & "<br>"
insertDone = True
End If
End If
Next i
html = html & "</body></html>"
BuildEmailBody = html
End Function
Sub FillEmailTable(wsEmail As Worksheet, rw As ListRow)
Dim tbl As ListObject
Dim headerCell As Range
Dim headerRow As Long
Dim dataRow As Long
Dim col As Long
Dim foundCol As Range
Set tbl = rw.Parent
Set headerCell = wsEmail.Cells.Find( _
What:=tbl.HeaderRowRange.Cells(1, 1).Value, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If headerCell Is Nothing Then
MsgBox "Header not found in 1stEmail sheet!", vbCritical
Exit Sub
End If
headerRow = headerCell.Row
dataRow = headerRow + 1
wsEmail.Rows(dataRow).ClearContents
For col = 1 To tbl.ListColumns.Count
Set foundCol = wsEmail.Rows(headerRow).Find( _
What:=tbl.HeaderRowRange.Cells(1, col).Value, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Not foundCol Is Nothing Then
wsEmail.Cells(dataRow, foundCol.Column).Value = _
rw.Range.Cells(1, col).Value
End If
Next col
End Sub
Function GetColumnIndex(tbl As ListObject, columnName As String) As Long
Dim col As ListColumn
For Each col In tbl.ListColumns
If LCase(col.Name) = LCase(columnName) Then
GetColumnIndex = col.Index
Exit Function
End If
Next col
GetColumnIndex = 0
End Function