Active Worksheet to PDF to Email VBA code

Hi all, I've spent the good part of today trying to solve this VBA code but with no tangible success. I am very inexperienced in Macros and VBA but at least I tried.

I want to be able to turn a worksheet into a PDF that is then emailed.

I found a code online but for some reason it doesn't seem to work. vba error msg.PNGvba error line.PNG

If I understand parts of the code correctly
I want the "Title" to be cell C6 and also to be part of the PDF file name, if this file is being saved. If the file is not being saved then it doesn't need to have Title in it I guess.
I want a string called "Name" to be cell G6 wherein this is the "To:" field of the email being sent.

I have attached the workbook as well.

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")

' 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 = "..." ' <-- Put email of the recipient here
.CC = "..." ' <-- Put email of 'copy to' recipient here
.Body = "Hi," & vbLf & vbLf _
& "The report is attached in PDF format." & 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

.
This will provide the TO in cell G6 :

Please Login or Register  to view this content.

You can utilize the same format for the CC as well.


Not certain what you mean by TITLE when saved or not saved ???

Thank you for the assistance on the To part in cell g6. My comment about the file being saved referred to the Kill PDF file command near the end. No need to stress over this as it is also insignificant really.

Were u able to get the overall code to work though?

My main dilemma is that code doesnt run fully. There is a "Run time error 91" that points to the line with OutlApp.CreateItem(0) still exists.

Also, I dont know if there are problems with other parts of the code. Ideally I wanted someone to either use the code given as a template to fix whatever errors there might be or help me to come up with another code that works.

I hope this clarifies.

Ok this morning somehow the entire code is working. So I am just fiddling with a few lines here and there to customize it. Thank you very much for your assistance.

.
You are welcome.