I have a workbook with multiple worksheets. I need to extract one sheet as a pdf, and attach it to an email. I have found ways to do this by creating a new workbook for the single sheet, renaming and then saving. However, I can't get the code to work for me. I know I am missing something. Please help. If I need to save the new worksheet, I would like it to reference the text in N3.
Side note. I don't need to keep the PDF after it is sent, so if there is a temporary save and then code to delete, please let me know.
Sub EmailPDF()
Dim MyFileName As String
Dim strNewFolderName As String
strNewFolderName = "SHIPPING INSTRUCTIONS"
If Len(Dir("T:\EXCEL000\BL\BL PDF\" & strNewFolderName, vbDirectory)) = 0 Then
MkDir ("T:\EXCEL000\BL\BL PDF\" & strNewFolderName)
End If
MyFileName = Range("N3")
ActiveWorkbook.Worksheets("CALLEJA").Copy
ActiveWorkbook.ExportAsFixedFormat xlTypePDF, "T:\EXCEL000\BL\BL PDF\SHIPPING INSTRUCTIONS\" & MyFileName & ".pdf"
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
With OutlApp.CreateItem(0)
.To = ""
.CC = ""
.Subject = "SHIPPING INSTRUCTIONS" & MyFileName
.Body = ""
.Attachments.Add "T:\EXCEL000\BL\BL PDF\" & MyFileName & ".pdf"
.Display
'.Send
End With
ActiveWorkbook.Close False
End Sub
Bookmarks