Emails from Excel (via VBA)

[Rile1]

Hi

Need your assistance on this

I have the following table and I am trying to
- automatically send an email via Excel based on the Status (Column 5) to the Travel Asst. (Column 22, as 1st notification point) and Travel Manager (Column 23, as escalation)
- in the escalation, copy the Travel Asst. and the copy the Travel Manager
- if there is a possibility to select a range of columns, and keep it in the body of the email (just a wish list)

Table

1	2	3	4	5	22	23
Date	Status	Travel Asst. Name	Booking Asst. Name	Status	Travel Asst. Email	Travel Manager Email
27-Aug-18	Pending Booking	ABC	123	Escalation to Travel Manager	abc@gmail.com	123@gmail.com
27-Aug-18	Pending Presentation	DEF	456	1st Notification to Travel Asst.	yxz@gmail.com	345@gmail.com
27-Aug-18	Pending Presentation	ZXY	789	2nd Notification to Travel Asst.	abc@gmail.com	123@gmail.com
27-Aug-18	Pending Payment	KLM	101	Escalation to Booking Manager	yxz@gmail.com	345@gmail.com

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

The code that i am trying to use for the same is the following
(Note: i am not sure if it is correct, as i tried to understand it from the web and modify it)

The error that displays when i try to run it is
Run- Time error"-2147467259 (80004005)':
We need to know who to send this to. Make sure you enter at least one name.

Note: there are 49 columns in the workbook

Start Code

Sub SendEscalationtoTravelManagerMail()
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim iCounter As Integer
Dim MailDest As String


Set OutLookApp = CreateObject("OutLook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)


With OutLookMailItem
For iCounter = 1 To WorksheetFunction.CountA(Columns(49))
If MailDest = "1st Notification to Travel Asst." And Cells(iCounter, 5) = "1st Notification to Travel Asst." Then
MailDest = Cells(iCounter, 22).Value
ElseIf MailDest = "2nd Notification to Travel Asst." And Cells(iCounter, 5) = "2nd Notification to Travel Asst." Then
MailDest = Cells(iCounter, 22).Value
ElseIf MailDest = "Escalation to Travel Manager" And Cells(iCounter, 5) = "Escalation to Travel Manager" Then
MailDest = MailDest & ";" & Cells(iCounter, 23).Value
End If
Next iCounter


.BCC = MailDest
.Subject = "Pending Booking"
.Body = "Reminder: Booking is due. Please complete."
.Send
End With


End Sub

End Code

Please if somebody could assist me on this

Thanking you

 

[davesexcel]

Your code does indicate the ".to:" address

 

[Rile1]

Hi Dave

Thanks for advising but I was not to clear on the same
I tried to amend the BCC to To but still it is unable to email

.To = MailDest
.Subject = "Pending Booking"
.Body = "Reminder: Booking is due. Please complete."
.Send

Please advise on the above to the "To" is to be added or is it on a different section

Thanking you

 

[davesexcel]

 If MailDest = "1st Notification to Travel Asst." And Cells(iCounter, 5) = "1st Notification to Travel Asst." Then

"MailDest" is not set so the criteria will never be met.

Possibly seeing if the cell.value = "1st Notification to Travel Asst." then adding the e-mail address to the string would be a solution.

for example:
To find the email addresses for "1st Notification to Travel Asst."

        For iCounter = 1 To WorksheetFunction.CountA(Columns(49))
            If Cells(iCounter, 5) = "1st Notification to Travel Asst." Then
                MailDest = Cells(iCounter, 23).Value & ";" & MailDest
            End If
        Next iCounter

 

[Rile1]

Hi Dave

Sorry did not get back earlier, but thanking you for your help

Thanks