Macro - Save and Send as xps button

itshewe

New Member
Joined
Jul 10, 2014
Messages
10
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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

Code:
[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
 
Last edited:
Upvote 0
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

Code:
[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
 
Last edited:
Upvote 0
If you take the "on error resume next" out you will be abble to identify the error.

Maybe this then
Code:
[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
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top