Sub Email_send()
Dim pl, pm As Worksheet
Dim x, rng, cel As Range
Dim i, wk, n As Long
Dim shd, b, c, d, e, n2, n3 As String
Dim OutApp As Object
Dim OutMail As Object
Dim OrigWB As Workbook
Dim std, edd As Date
Set pl = Sheets("PC List")
Set pm = Sheets("PC Confirmers")
Set OrigWB = ThisWorkbook
Application.ScreenUpdating = False
wk = Application.InputBox("Send Email for what week", "Week", , , , , , 1)
pm.Activate
For Each x In pm.Range([A2], pm.Cells(Rows.Count, "A").End(xlUp))
For Each cel In pl.Range("K3:K" & pl.Cells(Rows.Count, "K").End(xlUp).Row)
If cel.Value = x.Offset(, 1).Value And cel.Offset(, 1).Value = wk Then
n = cel.Row
End If
Next
std = DateSerial(Format(Now, "yyyy"), 1, 1) - WorksheetFunction.Weekday(DateSerial(Format(Now, "yyyy"), 1, 1), 2) + (wk - 1) * 7 + 1
edd = DateSerial(Format(Now, "yyyy"), 1, 1) - WorksheetFunction.Weekday(DateSerial(Format(Now, "yyyy"), 1, 1), 2) + wk * 7
b = pl.Cells(n, "B").Value
c = pl.Cells(n, "C").Value
d = pl.Cells(n, "D").Value
e = pl.Cells(n, "E").Value
n2 = pl.Cells(2, "N").Value
n3 = pl.Cells(3, "N").Value
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
OutMail.to = x.Offset(, 3).Value
OutMail.CC = x.Offset(, 4).Value
OutMail.Subject = "Process Confirmation (PC) assigned to " _
& x.Offset(, 1).Value & " for calendar week " & wk & " of Year " _
& Format(Now, "yyyy")
OutMail.HtmlBody = "Dear <B>" & x.Offset(, 1).Value & "</B>," & "<br><br>" _
& "You are requested to complete Process Confirmation (PC) as per details below" & "<br><br>" _
& "Process Confirmation (PC) for Week - <B>" & wk & "</B> of Year <B>" & Format(Now, "yyyy") & "(" & Format(std, "dd-mmm-yyyy") & "</B> to <B>" & Format(edd, "dd-mmm-yyyy") & ")" & "</B><br>" _
& "Process Confirmation (PC) name - <B>" & b & "</B><br>" _
& "Process Confirmation (PC) ID or Number - <B>" & c & "</B><br>" _
& "Function - <B>" & d & "</B><br>" _
& "Type - <B>" & e & "</B><br>" _
& "After completion of above assigned Process Confirmation (PC) you are requested to submit your observations in MAVIM and …." & "<br>" _
& "1. Please Provide Feedback to confirmee on execution process, if any" & "<br>" _
& "2. Please ask confirmee for improvement ideas, if any" & "<br>" _
& "3. Kindly place (Documentation) Improvement idea(s) and training need(s) VMS board/functional meeting. " & "<br><br>" _
& "Regards," & "<br><br>" _
& "<B>" & n2 & "</B><br>" _
& "<B>" & n3 & "</B><br>" _
& "APM Terminals Mumbai" & "<br>" _
& "GTI House, JN Port Sheva. Tal.: Uran." & "<br>" _
& "Dist.: Raigad. Navi Mumbai. 400707" & "<br>" _
& "[email protected]" & "<br><br>" _
& "Lifting Global Trade" & "<br>" _
& "www.apmterminals.com" & "<br>" _
& "www.apmtmumbai.com"
'OutMail.Display
OutMail.Send
Set OutMail = Nothing
Set OutApp = Nothing
Next x
With Application
.CutCopyMode = False
.ScreenUpdating = True
End With
End Sub
Sub Email_remind()
Dim pl, pm As Worksheet
Dim x, rng, cel As Range
Dim i, wk, n As Long
Dim shd, b, c, d, e, n2, n3 As String
Dim OutApp As Object
Dim OutMail As Object
Dim OrigWB As Workbook
Dim std, edd As Date
Set pl = Sheets("PC List")
Set pm = Sheets("PC Confirmers")
Set OrigWB = ThisWorkbook
Application.ScreenUpdating = False
wk = Application.InputBox("Send Email for what week", "Week", , , , , , 1)
pm.Activate
For Each x In pm.Range([A2], pm.Cells(Rows.Count, "A").End(xlUp))
For Each cel In pl.Range("K3:K" & pl.Cells(Rows.Count, "K").End(xlUp).Row)
If cel.Value = x.Offset(, 1).Value And cel.Offset(, 1).Value = wk Then
n = cel.Row
End If
Next
std = DateSerial(Format(Now, "yyyy"), 1, 1) - WorksheetFunction.Weekday(DateSerial(Format(Now, "yyyy"), 1, 1), 2) + (wk - 1) * 7 + 1
edd = DateSerial(Format(Now, "yyyy"), 1, 1) - WorksheetFunction.Weekday(DateSerial(Format(Now, "yyyy"), 1, 1), 2) + wk * 7
b = pl.Cells(n, "B").Value
c = pl.Cells(n, "C").Value
d = pl.Cells(n, "D").Value
e = pl.Cells(n, "E").Value
n2 = pl.Cells(2, "N").Value
n3 = pl.Cells(3, "N").Value
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
OutMail.to = x.Offset(, 3).Value
OutMail.CC = x.Offset(, 4).Value
OutMail.Subject = "Reminder - Process Confirmation (PC) assigned to " _
& x.Offset(, 1).Value & " for calendar week " & wk & " of Year " _
& Format(Now, "yyyy")
OutMail.HtmlBody = "Dear <B>" & x.Offset(, 1).Value & "</B>," & "<br><br>" _
& "You are requested to complete Process Confirmation (PC) as per details below" & "<br><br>" _
& "Process Confirmation (PC) for Week - <B>" & wk & "</B> of Year <B>" & Format(Now, "yyyy") & "(" & Format(std, "dd-mmm-yyyy") & "</B> to <B>" & Format(edd, "dd-mmm-yyyy") & ")" & "</B><br>" _
& "Process Confirmation (PC) name - <B>" & b & "</B><br>" _
& "Process Confirmation (PC) ID or Number - <B>" & c & "</B><br>" _
& "Function - <B>" & d & "</B><br>" _
& "Type - <B>" & e & "</B><br>" _
& "After completion of above assigned Process Confirmation (PC) you are requested to submit your observations in MAVIM and …." & "<br>" _
& "1. Please Provide Feedback to confirmee on execution process, if any" & "<br>" _
& "2. Please ask confirmee for improvement ideas, if any" & "<br>" _
& "3. Kindly place (Documentation) Improvement idea(s) and training need(s) VMS board/functional meeting. " & "<br><br>" _
& "Regards," & "<br><br>" _
& "<B>" & n2 & "</B><br>" _
& "<B>" & n3 & "</B><br>" _
& "APM Terminals Mumbai" & "<br>" _
& "GTI House, JN Port Sheva. Tal.: Uran." & "<br>" _
& "Dist.: Raigad. Navi Mumbai. 400707" & "<br>" _
& "[email protected]" & "<br><br>" _
& "Lifting Global Trade" & "<br>" _
& "www.apmterminals.com" & "<br>" _
& "www.apmtmumbai.com"
'OutMail.Display
OutMail.Send
Set OutMail = Nothing
Set OutApp = Nothing
Next x
With Application
.CutCopyMode = False
.ScreenUpdating = True
End With
End Sub
Bookmarks