Hi all,
I am current in the process of trying to automatically send emails via outlook from data found in my excel spreadsheet. Here's what I've done so far;
The above script is working perfectly but im looking to compose the email a bit more in depth. See https://imgur.com/Ko2gRRM for a visual of the table i am currently using for my data
I'm looking to reference call D that currently shows the status to appear on the end of the current text (Script above). So the email will read "Your request has been Approved" for example.
Hope this makes sense and would appreciate any help on this.
I am current in the process of trying to automatically send emails via outlook from data found in my excel spreadsheet. Here's what I've done so far;
Code:
Sub Send_email()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("email")
Dim Outlook As Object
Dim msg As Object
Set Outlook = CreateObject("outlook.application")
Dim i As Integer
Dim last_row As Integer
last_row = Application.WorksheetFunction.CountA(sh.Range("B:B"))
For i = 2 To last_row
Set msg = Outlook.createitem(0)
If sh.Range("E" & i) = "" Then
msg.To = sh.Range("B" & i).Value
msg.bcc = sh.Range("C" & i).Value
msg.Subject = "Request Status"
msg.body = "Your request has been"
msg.display
sh.Range("E" & i).Value = "sent"
Else
sh.Range("E" & i) = "Sent"
End If
Next i
MsgBox "Email sent"
End Sub
The above script is working perfectly but im looking to compose the email a bit more in depth. See https://imgur.com/Ko2gRRM for a visual of the table i am currently using for my data
Code:
msg.body = "Your request has been"
I'm looking to reference call D that currently shows the status to appear on the end of the current text (Script above). So the email will read "Your request has been Approved" for example.
Hope this makes sense and would appreciate any help on this.