Follow this video to learn how to build a useful excel file that can generate a PDF and attach it to an email in Outlook. I will cover the layout and the VBA codes so you can follow through and build on your own.
Support this channel on Patreon:
/ excelhighway
------------------------------
Track: Travel With Us - Vendredi [Audio Library Release]
Music provided by Audio Library Plus
Watch: • Travel With Us - Vendr...
Free Download / Stream: alplus.io/trav...
Sub Refresh_Input()
Dim ws As Worksheet, wd As Worksheet
Application.ScreenUpdating = False
Set ws = Sheets("Master")
Set wd = Sheets("Input")
Application.DisplayAlerts = False
wd.Delete
Application.DisplayAlerts = True
ws.Copy After:=Worksheets(Sheets.Count)
On Error Resume Next
ActiveSheet.name = "Input"
Application.ScreenUpdating = True
End Sub
Sub PDF()
Dim Row As Long, i As Long
Dim xFolder As String, name As String
Dim xOutlookObj As Object, EmailObj As Object
Dim ws As Worksheet
'Exit if you are in master sheet
If ActiveSheet.name = "Master" Then Exit Sub
'Delete lines w/o X only if column A is selection
If ActiveSheet.Range("A1") = "Selection" Then
Row = 100
For i = Row To 1 Step -1
If Cells(i, 1) = x Then
Rows(i).Delete
End If
Next
Columns("A:A").Delete
End If
'Save pdf on desktop
Set ws = Sheets("Lists")
name = ws.Range("A5")
xFolder = Environ("USERPROFILE") & "\Desktop" & "\" & name & ".pdf"
ActiveSheet.Columns("A:B").ExportAsFixedFormat Type:=xlTypePDF, Filename:=xFolder
'Create Outlook email
Set xOutlookObj = CreateObject("Outlook.Application")
Set xEmailObj = xOutlookObj.CreateItem(0)
With xEmailObj
.Display
.To = ""
.CC = ""
.Subject = ws.Range("D2")
.Attachments.Add xFolder
.HTMLbody = ws.Range("D3") & "br" & "br" & ws.Range("D4") & "br" & "br" & ws.Range("D5")
End With
End Sub