Hi All!
So I have a project I need finished by today and I'm stuck with a couple things.
I have a code that will take info from an excel workbook, convert it to a PDF then send as an email attachment. The excel workbook itself will look like the following:
It should take A1, B1, export it as a PDF , attach the PDF to an email and send to the email in C1. Then automatically restart the script to the next row, aka A2, B2, C2, then repeat until there is no more information on the next line
Here is the code I have so far. Any help is EXTREMELY appreciated!!! Been stuck on the above bit for a while now..
So I have a project I need finished by today and I'm stuck with a couple things.
I have a code that will take info from an excel workbook, convert it to a PDF then send as an email attachment. The excel workbook itself will look like the following:
It should take A1, B1, export it as a PDF , attach the PDF to an email and send to the email in C1. Then automatically restart the script to the next row, aka A2, B2, C2, then repeat until there is no more information on the next line
Here is the code I have so far. Any help is EXTREMELY appreciated!!! Been stuck on the above bit for a while now..
VBA Code:
Sub AttachActiveSheetPDF()
Dim IsCreated As Boolean
Dim i As Long
Dim PdfFile As String, Title As String
Dim OutlApp As Object
' Not sure for what the Title is
Title = Range("A1,B1")
' Define PDF filename
PdfFile = ActiveWorkbook.FullName
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)
PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"
' Export activesheet as PDF
With ActiveSheet
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
' Use already open Outlook if possible
On Error Resume Next
Set OutlApp = GetObject(, "Outlook.Application")
If Err Then
Set OutlApp = CreateObject("Outlook.Application")
IsCreated = True
End If
OutlApp.Visible = True
On Error GoTo 0
' Prepare e-mail with PDF attachment
With OutlApp.CreateItem(0)
' Prepare e-mail
.Subject = Title
.To = "example@example.com" ' <-- Put email of the recipient here
.CC = "" ' <-- Put email of 'copy to' recipient here
.Body = "Welcome to Example!" & vbLf & vbLf _
& "This email contains your Example Inc temporary credentials." & vbLf & vbLf _
& "Regards," & vbLf _
& Application.UserName & vbLf & vbLf
.Attachments.Add PdfFile
' Try to send
On Error Resume Next
.Send
Application.Visible = True
If Err Then
MsgBox "E-mail was not sent", vbExclamation
Else
MsgBox "E-mail successfully sent", vbInformation
End If
On Error GoTo 0
End With
' Delete PDF file
Kill PdfFile
' Quit Outlook if it was created by this code
If IsCreated Then OutlApp.Quit
' Release the memory of object variable
Set OutlApp = Nothing
End Sub