Option Explicit
Sub pdf()
Dim wsA As Worksheet, wbA As Workbook, strTime As String
Dim strName As String, strPath As String
Dim strFile As String
Dim strPathFile As String
'On Error GoTo errHandler
Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")
'create default name for savng file
strPath = "C:\PDFs\"
strFile = Sheets("Email").Range("B2").Value
strPathFile = strPath & strFile
Dim myFolder$
myFolder = "C:\PDFs"
If Dir(myFolder, vbDirectory) = "" Then
MkDir myFolder
End If
'export to PDF if a folder was selected
wsA.ExportAsFixedFormat 0, strPathFile
If Len(Dir$(myFolder)) > 0 Then
SetAttr myFolder, vbNormal
End If
'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& strPathFile
exitHandler:
Exit Sub
errHandler:
MsgBox "Could not create PDF file"
Resume exitHandler
End Sub
Sub Mail_workbook_Outlook()
Dim c As Range
Dim OutApp As Object
Dim OutMail As Object
Dim strPath As String
Dim FileName As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strPath = "C:\PDFs\"
FileName = Dir(strPath & "*.*")
'On Error Resume Next
For Each c In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Cells
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = c.Value
.CC = ""
.BCC = ""
.Subject = c.Offset(0, 1).Value
.Body = "The parts have been placed on today's load sheet and will be processed by EOB today. The parts have also been transferred to the repository file."
FileName = Dir(strPath & "*.*")
.Attachments.Add strPath & FileName
'.Send '<-- .Send will auto send email without review
.Display '<-- .Display will show the email first for review
End With
'On Error GoTo 0
Next c
Set OutMail = Nothing
Set OutApp = Nothing
byby
End Sub
Sub byby() 'deletes PDF file after attaching to email
Dim folder As Object
Dim path As String
path = "C:\PDFs"
Set folder = CreateObject("scripting.filesystemobject")
folder.DeleteFolder path, True
End Sub
