Macro - Save and Send as xps button

[itshewe]

Dear guys,

I have been trying to create a macro for a while that can save and send as XPS but without any luck. I'm using relative references and this is the results:

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypeXPS, Filename:= _
"C:\Users\itshewe\To do.xps", Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
Application.Dialogs(xlDialogSendMail).Show

However, when running the macro the file format becomes .xlsx

I would highly appreciate any help.


Kind regards,

itshewe

 

[Kamolga]

This is normal, if you make a save as .xps, you will notice that you have an xps file saved somewhere and your workbook stays open, so using application.dialogs will sned your workbook, not the xps version. instead you will have to do Something like

[COLOR=blue]Sub[/COLOR] Mail()



ActiveWorkbook.ExportAsFixedFormat Type:=xlTypeXPS, Filename:= _
        "C:\Users\itshewe\To do.xps", Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False


    [COLOR=blue]Dim[/COLOR] OutApp [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR]
    [COLOR=blue]Dim[/COLOR] OutMail [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR]

    [COLOR=blue]Set[/COLOR] OutApp = CreateObject([COLOR=#a31515]"Outlook.Application"[/COLOR])
    [COLOR=blue]Set[/COLOR] OutMail = OutApp.CreateItem(0)

    [COLOR=blue]On[/COLOR] [COLOR=blue]Error[/COLOR] [COLOR=blue]Resume[/COLOR] [COLOR=blue]Next[/COLOR]
   [COLOR=green]' Change the mail address and subject in the macro before you run it.[/COLOR]
    [COLOR=blue]With[/COLOR] OutMail
        .[COLOR=blue]To[/COLOR] = [COLOR=#a31515]"xxx@abc.com"[/COLOR]
        .CC = [COLOR=#a31515]""[/COLOR]
        .BCC = [COLOR=#a31515]""[/COLOR]
        .Subject = [COLOR=#a31515]"This is the Subject line"[/COLOR]
        .Body = [COLOR=#a31515]"Hello World!"[/COLOR]
        .Attachments.Add ("C:\Users\itshewe\To do.xps")

End sub

 

[itshewe]

Dear Kamolga,

thank you you for your time and answer. However, nothing really happens when I run the macro.

Have you tried to run and example yourself, bc nothing happens here.

kind regards
Itshewe

This is normal, if you make a save as .xps, you will notice that you have an xps file saved somewhere and your workbook stays open, so using application.dialogs will sned your workbook, not the xps version. instead you will have to do Something like

[COLOR=blue]Sub[/COLOR] Mail()



ActiveWorkbook.ExportAsFixedFormat Type:=xlTypeXPS, Filename:= _
        "C:\Users\itshewe\To do.xps", Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False


    [COLOR=blue]Dim[/COLOR] OutApp [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR]
    [COLOR=blue]Dim[/COLOR] OutMail [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR]

    [COLOR=blue]Set[/COLOR] OutApp = CreateObject([COLOR=#a31515]"Outlook.Application"[/COLOR])
    [COLOR=blue]Set[/COLOR] OutMail = OutApp.CreateItem(0)

    [COLOR=blue]On[/COLOR] [COLOR=blue]Error[/COLOR] [COLOR=blue]Resume[/COLOR] [COLOR=blue]Next[/COLOR]
   [COLOR=green]' Change the mail address and subject in the macro before you run it.[/COLOR]
    [COLOR=blue]With[/COLOR] OutMail
        .[COLOR=blue]To[/COLOR] = [COLOR=#a31515]"xxx@abc.com"[/COLOR]
        .CC = [COLOR=#a31515]""[/COLOR]
        .BCC = [COLOR=#a31515]""[/COLOR]
        .Subject = [COLOR=#a31515]"This is the Subject line"[/COLOR]
        .Body = [COLOR=#a31515]"Hello World!"[/COLOR]
        .Attachments.Add ("C:\Users\itshewe\To do.xps")

End sub

 

[Kamolga]

If you take the "on error resume next" out you will be abble to identify the error.

Maybe this then

[COLOR=#333333][I]ActiveWorkbook.ExportAsFixedFormat Type:=xlTypeXPS, Filename:= _[/I][/COLOR] 
"C:\Users\itshewe\To do.xps", Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:= [COLOR=#333333][I]False

[/I][/COLOR]'When in VBA window, tools/reference and activate microsoft outlook XX.XXX object library -> If not, user define errorDim App As Outlook.Application
Dim NewMail As MailItem




    Set App = CreateObject("Outlook.Application")
    Set NewMail = App.CreateItem(olMailItem)


NewMail.To = Mailto
    NewMail.CC = Mailcc
    NewMail.Body = Mailbody
    NewMail.Subject = Mailsubject
    NewMail.Attachments.Add ("[COLOR=#333333][I]C:\Users\itshewe\To do.xps[/I][/COLOR]")
    NewMail.Display