Hi all,
below is current status of code as is, with the exception of a redacted email.
It works just fine, however it saves the excel to the desktop as a PDF and attaches it to the email as a PDF
Instead, I need it to save to desktop as PDF but still email as an excel document - can this be done as a single button?
Im no expert by any means but I dont know where to begin looking for this.
below is current status of code as is, with the exception of a redacted email.
It works just fine, however it saves the excel to the desktop as a PDF and attaches it to the email as a PDF
Instead, I need it to save to desktop as PDF but still email as an excel document - can this be done as a single button?
Im no expert by any means but I dont know where to begin looking for this.
Code:
Sub Saveaspdfandsend()
Dim xSht As Worksheet
Dim xFile As String
Dim xYesorNo As Integer
Dim xOutlookObj As Object
Dim xEmailObj As Object
Dim xUsedRng As Range
Set xSht = ActiveSheet
fNme = xSht.Range("A65").Value
xFile = "C:\Users\" & Range("N64").Value & "\Desktop" & "\" & fNme & ".pdf"
'Check if file already exist
If Len(Dir(xFile)) > 0 Then
xYesorNo = MsgBox(xFile & " already exists." & vbCrLf & vbCrLf & "Do you want to overwrite it?", _
vbYesNo + vbQuestion, "File Exists")
On Error Resume Next
If xYesorNo = vbYes Then
Kill xFile
Else
MsgBox "if you don't overwrite the existing PDF, I can't continue." _
& vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Exiting Macro"
Exit Sub
End If
If Err.Number <> 0 Then
MsgBox "Unable to delete existing file. Please make sure the file is not open or write protected." _
& vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Unable to Delete File"
Exit Sub
End If
End If
Set xUsedRng = xSht.UsedRange
If Application.WorksheetFunction.CountA(xUsedRng.Cells) <> 0 Then
'Save as PDF file
xSht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=xFile, Quality:=xlQualityStandard
'Create Outlook email
Set xOutlookObj = CreateObject("Outlook.Application")
Set xEmailObj = xOutlookObj.CreateItem(0)
With xEmailObj
.Display
.to = "REDACTED"
.CC = ""
.Subject = "New " & Range("A65").Value
.Attachments.Add xFile
If DisplayEmail = False Then
'.Send
End If
End With
Else
MsgBox "The active worksheet cannot be blank"
Exit Sub
End If
End Sub