Email Sheet

mjones18

New Member
Joined
Jan 24, 2017
Messages
8
Hello,

I am trying to write an email macro. The Excel sheet currently runs a macro in the background that pulls data from a database and puts it in an excel tab. The excel sheet has numerous tabs of dates with the data. After the current macro runs and updates a tab with today's date, I want to have code that says:

If the sheet = today's date, email the sheet to my email address? Does anyone have any ideas how I can do this?

Thank you!
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
Hi,

Here is something I wrote earlier. It sends the active sheet to an email address.
Rich (BB code):
Sub Email_Worksheet_As_Workbook()
    ActiveSheet.Copy
    With ActiveWorkbook
        .Windows(1).Visible = False
        Application.DisplayAlerts = False
        .SaveAs Environ("TMP") & "\tmp.xlsx", FileFormat:=xlWorkbookDefault, ConflictResolution:=xlLocalSessionChanges
        Application.DisplayAlerts = True
        .Close (True)
    End With
    With CreateObject("Outlook.Application").CreateItem(0)
        .To = "RickXL@xxx.com"
        .Subject = "Worksheet: " & ActiveSheet.Name
        .Body = ""
        .Attachments.Add Environ("TMP") & "\tmp.xlsx"
        '.display
        .send
    End With
End Sub
You will need to add something to check whether the worksheet name is right and also add some way of triggering the macro.

It makes a copy of the active sheet and it saves it to your TMP folder. From there it creates an email and makes the workbook an attachment.
The email address, in red, will need to be changed to your email address.

I hope this helps.


Regards,
 

mjones18

New Member
Joined
Jan 24, 2017
Messages
8
Email Macro - Excel to PDF

Hello,

I have created an email macro that currently emails me the active excel sheet as a pdf. The only problem is the pdf comes in 14 pages portrait mode and doesn't show that full extent of the excel sheet. Does anyone know how to reconfigure this code to show the full excel sheet as a pdf so that I only get 1 page? Thanks!

Sub DailyEmail()
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:="C:\Filename.pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
'.To = ""
'Uncomment the line below to hard code a subject
'.Subject = ""
.Attachments.Add "C:\Filename.pdf"
.display
.send
End With

'Delete the pdf
Kill "C:\Filename.pdf"

ActiveWorkbook.Close (False)


End Sub
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
Thanks for letting me know.

One thing I did notice is that you might want to get rid of this line:
Code:
.Windows(1).Visible = False
It makes it look as if the received workbook is empty.


Regards,
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,082
Re: Email Macro - Excel to PDF

Do it the same way you do it manually. This means record a macro to see the syntax. This has to do with the sheet's PageSetup. You want autofit. Record the macro as if you were printing the range or Print Area. You can also do the same in Page Layout on the ribbon.

Tip, paste code between tags inserted by clicking # icon on message's menu.
 

Forum statistics

Threads
1,077,855
Messages
5,336,790
Members
399,102
Latest member
chudson1

Some videos you may like

This Week's Hot Topics

Top