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!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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,
 
Upvote 0
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
 
Upvote 0
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,
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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