Trying to get Excel to send a sheet as a PDF

[DipDip]

Hiya Peeps,
I wonder if someone can help me with this. I am trying to get the code to select the right data via autofilter, then email the sheet as a PDF.

I have the following code:

Sub Sort1()
'
    ActiveSheet.Range("$AB$4:$AB$350").AutoFilter Field:=1, Criteria1:=Array( _
        "Care Assistant", "RGN", "Senior Care ***..."), Operator:=xlFilterValues


ChDir "K:\Payroll\"
ActiveSheet.ExportAsFixedFormat Type:=x1TypePDF, Filename:= _
"K:\Payroll\testsave.pdf", OpenAfterPublish:=False


Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object


Set OutLookApp = CreateObject(“Outlook.application”)
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set myAttachments = OutLookMailItem.Attachments


With OutLookMailItem
.To = “”
.Subject = "Time Sheets"
.Body = "Please check these and let me know if they are correct or not."
myAttachments.Add "K:\Payroll\testsave.pdf"
‘.send
.Display
End With


Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
End Sub

However I get the following error:

Run-time error '424':

Object required

and this is the bit that is highlighted:

Set OutLookApp = CreateObject(“Outlook.application”)

Cheers with the help in advance peeps :D

 

[ranman256]

Do you have Outlook object library loaded in VBE, menu: tools, references?

 

[DipDip]

I have Outlook 16.0 object library ticked in that section.

 

[Fluff]

Looks like some of your double quotes are wrong

Set OutLookApp = CreateObject([COLOR=#ff0000]“[/COLOR]Outlook.application[COLOR=#ff0000]”[/COLOR])
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set myAttachments = OutLookMailItem.Attachments


With OutLookMailItem
.To =[COLOR=#ff0000] “”[/COLOR]

They should be "" not “”

 

[DipDip]

Looks like some of your double quotes are wrong

Set OutLookApp = CreateObject([COLOR=#ff0000]“[/COLOR]Outlook.application[COLOR=#ff0000]”[/COLOR])
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set myAttachments = OutLookMailItem.Attachments


With OutLookMailItem
.To =[COLOR=#ff0000] “”[/COLOR]

They should be "" not “”

Awesome! That sorted out that issue, but not it still comes back with the same error for the following bit of code:

‘.send

 

[Fluff]

Basically the same thing it should be ' not`

 

[DipDip]

Yeah just realised what I wrote and that I'm an idiot . Cheers everyone! Much appreciated!!!

 

[Fluff]

Glad we could help & thanks for the feedback