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
<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
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