[EXPERT] Need Macro excel help

[Aviate]

I would like to create a macro function such that when I press the “Send Email” button it will send out email reminders to staff to submit their medical documents and if possible able to attached an instruction guide along with the email.

With customized message. Such as “Dear ABC, kindly submit your medical documents “date”.

The logic should be: If MC Submitted = Yes, No action required. Else, MC Submitted = No, the customized message will be sent out.

I had made a command but unable to get it work. Not sure if the pros can help me out please.

Spoiler

Private Sub CommandButton1_Click()
Dim Names As String
Dim Date(s) As Integer
Dim Flight(s) / Duties As String
Dim Email As String

Dim ColumnNameDate As String
Dim ColumnNameMC Submitted and Uploaded in Prosoft (Y/N) As String
Dim ColumnNameEmail As String
Dim ColumnNameNames As String

Dim Date(s) As Date
Dim MC Submitted and Uploaded in Prosoft (Y/N) As String
Dim Text As String

Dim outlookOBJ As Outlook.Application
Dim mItem As Outlook.MailItem
Set outlookOBJ = New Outlook.Application



ColumnNameNames = "D"
ColumnNameDate(s) = "E"
ColumnNameFlight(s) / Duties = "F"
ColumnNameMC Submitted and Uploaded in Prosoft (Y/N) = "H"
ColumnNameEmail = "I"

RowNrNumeric = 2
RowNrString = RowNrNumeric
Documents = Range(ColumnNameDocuments + RowNrString).Value
DueDate(s) = Range(ColumnNameDate(s) + RowNrString).Value
MC Submitted and Uploaded in Prosoft (Y/N) = Range(ColumnNameMC Submitted and Uploaded in Prosoft (Y/N)+ RowNrString).Value
Names = Range(ColumnNameName + RowNrString).Value
Email = Range(ColumnNameEmail + RowNrString).Value

Do While Documents <> ""
Range(ColumnNameDate(s) + RowNrString).Interior.ColorIndex = 2
Set mItem = outlookOBJ.CreateItem(0)
If (MC Submitted and Uploaded in Prosoft (Y/N) = "No”) Then
Text = DueDate(s)
With mItem
.To = Email
.CC =
.BCC =
.Subject = "SUBMISSION OF MEDICAL CERTIFICATE: "
.Body = "Dear " + Names + ", DOCUMENT: MEDICAL CERTIFICATE for " + Text + ". Your compliance is greatly appreciated. Thank you. "
.Send
End With



Range(ColumnNameDate(s) + RowNrString).Interior.ColorIndex = 3
ElseIf MC Submitted and Uploaded in Prosoft (Y/N)= "OFF") Then
Range(ColumnNameDate(s) + RowNrString).Interior.ColorIndex = 3

End If


RowNrNumeric = RowNrNumeric + 1
RowNrString = RowNrNumeric
Documents = Range(ColumnNameDocuments + RowNrString).Value
DueDate(s) = Range(ColumnNameDate(s) + RowNrString).Value
MC Submitted and Uploaded in Prosoft (Y/N) = Range(ColumnNameMC Submitted and Uploaded in Prosoft (Y/N)+ RowNrString).Value
Names = Range(ColumnNameName + RowNrString).Value
Email = Range(ColumnNameEmail + RowNrString).ValueLoop

End Sub

 

[GreggMQ]

Have you already tried something?

 

[shawnbright]

Did you sort the issue now bro? We are here to help!