Print first page of worksheet to email

Hi Folks,

I have a worksheet that contains a template used for printing out in a set layout. The macro I have works fine for printing just the first page of the sheet but I am stumped as to how to send this to an email also. The macros I have come across only deal with worksheet or workbook, not individual pages.

The reason for wanting to email the as-printed page is that there are headers and footers which i can't capture when setting a normal range.

Many thanks

Hi GFIJame

Could you upload an example workbook so we could take a look at what you want copyed to e-mail?

I've attached a mock-up of the template I use in excel 2010 format.

Thanks for looking stojko

Template Example.xlsx

The range that should be copyed to the e-mail is always A1:I50 ?

It should always be the first page in its entirety, including header and footer.

You know if you want...you can attach this as a .pdf file so no one can change it.

PDF would be good but it's purely for reference when a PO is generated as the actual details are stored and just fed to the template on creation

Here try this example workbook you gave me. Make a folder under "C:\Files"
Open this workbook and hit Ctrl+Y
Tell me if it works and if you want it like this? :)

I am trying to download your example but for some reason it downloads as a php file and not the workbook. Not sure what the issue is.

Ok then never mind that
Open the example you uploaded.
Insert in Cell "H3": Type anything that could look like a PO numbe like 12312PONUM2012 - doesn't matter what
then open the VBA Editor and insert a new modul.
And copy this in the new modul: Then try runing the SendEmail Macro

Sub SendEmail()
Dim strFileName As String
Dim strFileLoc As String

    strFileName = ActiveSheet.Range("H3").Value
    strFileLoc = "C:\Files\"
    strBody = "Hi" & vbNewLine & _
              " " & vbNewLine & _
              "You can enter some text here if you want, for file name: " & strFileName & vbNewLine & _
              "And some more text under here :)" & vbNewLine & _
              " " & vbNewLine & _
              "Bye."

    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        strFileLoc & strFileName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True

Application.ScreenUpdating = 0
    Set ol = CreateObject("Outlook.application")
    Set NewMessage = ol.CreateItem(olMailItem)

With NewMessage
    .To = ""
    .Subject = ""
    .cc = ""
    .attachments.Add strFileLoc & strFileName & ".pdf"
    .body = strBody
    .Display
End With
Application.ScreenUpdating = 1
End Sub

Works like a charm Thanks kindly for your help, been wracking my brains for a day trying to puzzle it out!

Hehe, no problem, Just change the strFileLoc to where you want it to be save and change the strBody to say something if you want.
Glad I could help.
bye.