1 year ago

#387266

test-img

MarkAKE

Using VBA, save and share file as XLSM

I am using the following code to save an Excel sheet as PDF and send it as mail attachment.

I can't manage to simultaneously save it as xlsm file to the same path and file name and also attach it to an email that I have generated for the pdf file.

Dim varResult As Variant
Dim ActBook As Workbook

varResult = Application.GetSaveAsFilename(FileFilter:= _
            "PDF (*.pdf), *.pdf", Title:="Save File", _
             InitialFileName:="Example"


ActiveWorkbook.BuiltinDocumentProperties("Keywords") = "Example"
'PDF EXPORT
ActiveSheet.ExportAsFixedFormat Filename:=varResult, Type:=xlTypePDF, OpenAfterPublish:=True, _
IncludeDocProperties:=True


Dim objOutlook As Object
Dim objMail As Object

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)

With objMail
    .To = "Example@mail.com"
    .Subject = "Example"
    .Body = "Example"
    .Attachments.Add varResult
    
    .Send        
    End With

I tried to copy the "varResult" part and replace "pdf" with "xlsm".

ActiveSheet.ExportAsFixedFormat Filename:=varResult, Type:=xlOpenXMLWorkbookMacroEnabled, OpenAfterPublish:=False, _
  IncludeDocProperties:=True

I get

"Runtime error 75"

excel

vba

outlook

xlsm

0 Answers

Your Answer

Accepted video resources