bhuvanbalasubramanian/GeneratePdfAndSendEmail.vbs

## GeneratePdfAndSendEmail.vbs

Option Explicit
Public Pws As Worksheet, Sws As Worksheet, ParticipantName As String

Sub sendCertEMail()
    Dim count As Integer, NoOfParticipants As Integer, FilePath As String, ToEmail As String, ASMEmail As String

    Set Pws = Worksheets("SheetA")
    Set Sws = Worksheets("SheetB")
    NoOfParticipants = 10 'Set the no. of participants to send the books

    Call DeleteExisitingCerFiles

    For count = 2 To NoOfParticipants
        ParticipantName = Pws.Range("A" & count).Value
        ToEmail = Pws.Range("B" & count).Value
        ASMEmail = Pws.Range("C" & count).Value
        FilePath = ThisWorkbook.Path & "/" & ParticipantName & " - Book.pdf"

        Sws.Shapes("Txb").TextFrame.Characters.Text = ParticipantName
        Sws.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=FilePath, _
            Quality:=xlQualityStandard

        'Send email
        Call Send_Mail(ASMEmail, ParticipantName, ToEmail, FilePath)
    Next count

    MsgBox Title:="Task Box", Prompt:="Books sent successfully!"

End Sub

Private Sub Send_Mail(ASMEmail As String, ParticipantName As String, ToEmail As String, FilePath As String)
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        strbody = "<html><p>Dear " & ParticipantName & ",</p>" & _
                "<p>Enclosed is the training books.</p>" & _
                "<p>Best Regards,<br/>Bhuvaneswaran</p>" & _
                "<img src=""cid:signature.png"" height=100 width=300>"

        .To = ToEmail
        .CC = ASMEmail
        .Subject = "Training Books"
        .Attachments.Add FilePath
        .Attachments.Add ThisWorkbook.Path & "\signature.png", 1, 0
        .HTMLBody = strbody
        .Display
    End With
    On Error GoTo 0

    Set OutApp = Nothing
    Set OutMail = Nothing
End Sub

Private Sub DeleteExisitingCerFiles()
    On Error Resume Next
        Kill ThisWorkbook.Path & "/*.pdf"
    On Error GoTo 0
End Sub

	Option Explicit
	Public Pws As Worksheet, Sws As Worksheet, ParticipantName As String

	Sub sendCertEMail()
	Dim count As Integer, NoOfParticipants As Integer, FilePath As String, ToEmail As String, ASMEmail As String

	Set Pws = Worksheets("SheetA")
	Set Sws = Worksheets("SheetB")
	NoOfParticipants = 10 'Set the no. of participants to send the books

	Call DeleteExisitingCerFiles

	For count = 2 To NoOfParticipants
	ParticipantName = Pws.Range("A" & count).Value
	ToEmail = Pws.Range("B" & count).Value
	ASMEmail = Pws.Range("C" & count).Value
	FilePath = ThisWorkbook.Path & "/" & ParticipantName & " - Book.pdf"

	Sws.Shapes("Txb").TextFrame.Characters.Text = ParticipantName
	Sws.ExportAsFixedFormat Type:=xlTypePDF, _
	Filename:=FilePath, _
	Quality:=xlQualityStandard

	'Send email
	Call Send_Mail(ASMEmail, ParticipantName, ToEmail, FilePath)
	Next count

	MsgBox Title:="Task Box", Prompt:="Books sent successfully!"

	End Sub

	Private Sub Send_Mail(ASMEmail As String, ParticipantName As String, ToEmail As String, FilePath As String)
	Dim OutApp As Object
	Dim OutMail As Object
	Dim strbody As String

	Set OutApp = CreateObject("Outlook.application")
	Set OutMail = OutApp.CreateItem(0)

	On Error Resume Next
	With OutMail
	strbody = "<html><p>Dear " & ParticipantName & ",</p>" & _
	"<p>Enclosed is the training books.</p>" & _
	"<p>Best Regards,<br/>Bhuvaneswaran</p>" & _
	"<img src=""cid:signature.png"" height=100 width=300>"

	.To = ToEmail
	.CC = ASMEmail
	.Subject = "Training Books"
	.Attachments.Add FilePath
	.Attachments.Add ThisWorkbook.Path & "\signature.png", 1, 0
	.HTMLBody = strbody
	.Display
	End With
	On Error GoTo 0

	Set OutApp = Nothing
	Set OutMail = Nothing
	End Sub

	Private Sub DeleteExisitingCerFiles()
	On Error Resume Next
	Kill ThisWorkbook.Path & "/*.pdf"
	On Error GoTo 0
	End Sub