Software

Let Excel e-mail your weekly reports

If you have to send the same worksheet to a number of people every week, it probably takes you quite awhile to prepare the e-mail. If you have to do this for a number of reports, it can take even longer. Follow these steps to have Excel e-mail the reports for you.

If you have to send the same worksheet to a number of people every week, it probably takes you quite awhile to prepare the e-mail. If you have to do this for a number of reports, it can take even longer. Follow these steps to have Excel e-mail the reports for you:

  1. Open the workbook containing the report you want to send.
  2. Press [Alt][F11].
  3. In the Project-VBA Project pane, double-click ThisWorkbook.
  4. Go to Insert | Procedure.
  5. Click in the Name text box and enter WeeklyReportEmail. Click OK.
  6. At the prompt, enter the following code:

    Dim DistList As Variant

    DistList = Array("name1@company.com", "name2@company.com", "name3@company.com")

    ActiveWorkbook.Sendmail Recipients: = DistList
  7. Press [Alt]Q.
  8. Press [Alt][F8].
  9. Click WeeklyReportEmail in the Macro list.
  10. Click the Options button.
  11. In the Ctrl+ box, enter m. Click OK.

Now when you need to send a report to everyone on your distribution list just open the workbook and press [Ctrl]M.

Miss an Excel tip?

Check out the Microsoft Excel archive, and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

34 comments
chuckfab1
chuckfab1

Why go through all this. Why not just go to File Menu -> Send to -> Mail Recipients or Mail Recipients (as attachment)?

lester42
lester42

Is there a way to set up this to run if your workbooks need to be updated via links before being emailed out? Currently, I have to open Excel daily, hit Update Links, resave the file (as that day's file) and then email to a group. Thanks!

kile_ivr
kile_ivr

i wanted to subscribe for weekly updates but couldn`t. Thats brilliant!

bwr_rick
bwr_rick

Thanks Mary Ann! I send out my sheet daily and this worked just fine for me, but... I also would like to add a standard message in both the email subject line and in the email body section. The Excel file name in the subject line is fine, but a short message and my contact info in the body is much more professional. Any suggestions??? Thanks in advance!

TheVirtualOne
TheVirtualOne

I am stering away from anything that I can't do online. I'm suggesting to everyone who ins't tech savvy that they start using Google Apps unless they deserve to use a program that the company needs to pay for.

dvines
dvines

This is a great tool and it really helps me. I was wondering if it also works for Microsoft Word? If so, what changes should be made to the VBA script?

koreywinslow
koreywinslow

Thats a pretty good but you always have to keep it up to date. What I do it use a WSH with ado to read the data from the database and then place it into a recordset. After that, write the recordset to the excel file and email the file to me ant the others who need it. I have a task schedualed that runs my script daily @ 5 am. That way I never need to keep updating my excel file. Here is how to write to an excel using ado. Set cnn=createobject("ADODB.Connection") Set rst=createobject("ADODB.Recordset") cnn.open "DSN=YourDSN; UID=YourUserID; PWD=YourPassword;" rst.open "SQL Query",cnn Set xls=createobject("excel.application") with xls .displayalerts=False .workbooks.add .worksheets("sheet1").Select For i=0 To rst.fields.count -1 .cells(1,i+1).value=rst.fields.item(i).name Next row=2 Do until rst.eof For i=0 To rst.fields.count-1 .cells(row,i+1).value=rst.fields.item(i).value Next rst.movenext row=row+1 Loop .activeworkbook.saveas (path) .quit End with I let you figure out how to email.

cyslregistrar
cyslregistrar

I feel like a real dummy where do I insert the DistList (company1@company.com)

COFGFOR
COFGFOR

Application.MailSystem=xlNoMailSystem returns True on my PC, though I have a non-Microsoft "MailSystem". Can't use this tip, but I'd sure like to!

mohadadel
mohadadel

It's agreat solution and it's works fine with me, But the question is what if i have daily reports to diffrent email address? I am working on it :) Great job Mary

gadavie
gadavie

This looks great but seems to reply on Outlook or Outlook Express (that's what pops up on my screen even though my default mail is Lotus Notes). I don't want to start a debate on Notes vs Outlook here, but given I can't change my email program, is there a way to do this with Notes? Thanks

mw00110011
mw00110011

You can modify the basic code as follows: smResult = "Testing it ... " ActiveWorkbook.SendMail Recipients:=DistList, Subject:=smResult As far as getting something into the body - not sure.

lovindeer
lovindeer

I myself would like additional info on the above subject. It would be useful in my organisation

koreywinslow
koreywinslow

create a word template and insert bookmark (remember the names) then use the getobject(path of file,"word.application") dim object1 as object set object1=getobject(path of file,"word.application") with object1 .selection.goto what=wdGoToBookmark,Name="Name of Bookmark" .selection.typetext "your value (keep going until you finish all the bookmark you make) end with hint: use one standard name with an integer at the end so you can use a for loop

koreywinslow
koreywinslow

create a word template and insert bookmark (remember the names) then use the getobject(path of file,"word.application") dim object1 as object set object1=getobject(path of file,"word.application") with object1 .selection.goto what=wdGoToBookmark,Name="Name of Bookmark" .selection.typetext "your value (keep going until you finish all the bookmark you make) end with hint: use one standard name with an integer at the end so you can use a for loop

zringgold
zringgold

This may be a little advanced but I'll try anyway. I have standard charts and spreadsheets that have to be emailed to managers. My data is coming from an Access database. The problem is I have to manually run the query in Access, copy the data to Excel and reformat my charts. I know there has to be an easier way but I can't figure it out. Any help would be greatly appreciated. On another note, this is a great tip for sending the email messages.

IndyBob
IndyBob

Do you mean a non-Microsoft e-mail client or a non-Microsoft e-mail server? In either case, does anyone know a workaround for this. I use a non-MS e-mail client, but a Win2K3 server running a non-MS e-mail program. Sounds like I will have this same problem. Would really like to use this feature!

mtrevino57
mtrevino57

I have a spreadsheet that contains a list of e-mail addresses in individual cells. Through the use of checkboxes, I am able to select the recipients that I want to receive the worksheet. I have managed to concatenate all of the email addresses into a single string and store that in a single cell. What I need help with, I want to import the cell contents(i.e. the string of e-mails) into the Array Dirlist which would then e-mail each recipient a copy of the worksheet. The original code which does work is Public Sub EmailReport() Dim DistList As Variant DistList = Array("mtrevino57@yahoo.com", "mtrevino@gmail.com") ActiveWorkbook.SendMail Recipients:=DistList End Sub And what I would like to be able to do is store the cell contents which contains the "formatted e-mail addresses" into the array DirList.

bzitt
bzitt

I'm using Notes 6.5 and the example worked fine for me. Must be with your installation.

dhays
dhays

Not enough detail given to explain how to set up the email client. It seems to me it is much easier to just open Notes and attach the file. I put the information in a separate file to avoid having to send the whole analysis document. In short--More Information, Please!

mtrevino57
mtrevino57

I am thinking it would be possible to set Recipients:= "Some Cell Address" as well as Subject, and Body. This would then allow the user to maintain an e-mail list on another worksheet or cell range, and retrieve the body from yet another cell as well. I haven't tried this, but I intend to! GREAT IDEA!

mtrevino57
mtrevino57

I am thinking it would be possible to set Recipients:= "Some Cell Address" as well as Subject, and Body. This would then allow the user to maintain an e-mail list on another worksheet or cell range, and retrieve the body from yet another cell as well. I haven't tried this, but I intend to! GREAT IDEA!

ksawaneh
ksawaneh

Good day, how do i add comments, signature to this report

pat.barna
pat.barna

I did a lot of Access and Excel work some years back. Excel had a function under the "Data" menu to "get external data" then "New Database Query". You then queried right into the Access data table to set up your query in Excel. Once set up, the query can be saved and the Excel workbook saved - to update, open the Excel workbook then "Data" "Get External Data" and "Run Saved Query..."

kpennington
kpennington

It is possible using VBA and macros timed to run at specified intervals to read data from an Access datatable using ADO, directly into a spreadsheet. Using dynamic named ranges charts can be automatically updated for the new data as well as selecting sections of the spreadsheet. An Excel macro can send emails automatically and it is also possible to get around Outlook's security function using 'ClickYes'. This can happen without any intervention. Not easy and a challenge for you.

stindle
stindle

smResult = "Your subject line" shEmail = "The sheet where your eMail list is located" CurrentRow = 1 EndOfFile = Worksheets(shEmail).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row GetList = Worksheets(shEmail).Range("A" & CurrentRow) While CurrentRow < EndOfFile If CurrentRow > 1 Then GetList = GetList & ", " & Worksheets(shEmail).Range("A" & CurrentRow) CurrentRow = CurrentRow + 1 Wend Answer = MsgBox("You are about to send this file to " & GetList, vbOKCancel) If Answer = vbOK Then ActiveWorkbook.SendMail Recipients:=GetList, Subject:=smResult

wvenanci
wvenanci

Not sure how I can help out but I'm using LN 6.5.4 and Excel 2003, and the tip worked as advertised.

jmbrauer
jmbrauer

I also run Notes 6.5 but I get a series of Notes error messages, the 1st of which complains of a missing .nsf file. So there must be certain conventions to follow in Notes to make this work. Would like to know what they are so I could use this tip.

mtrevino57
mtrevino57

If you take a look at message BINGO! GOT IT! I was able to send from Excel and Groupwise using Excel's Sendmail Object which granted does have some limitations. If you are interested in the example sheet, you can grab it from http://mtrevino57.googlepages.com/home

stindle
stindle

I loaded the emails addresses into a hidden worksheet so the owner could edit the list. The only problem I have is Outlook pops up a warning that another program is attempting to send an eMail and the boss has to wait 5 seconds before she can click on the Yes button to finally send the file. I used Application.DisplayAlerts = False but I still need to find the Outlook code to turn the warnings off. Steve Public Sub WeeklyReportEmail() 'send file to DistList Dim DistList As Variant Dim Answer, EndOfFile, CurrentRow As Integer Dim GetList, shEmail As String Application.ScreenUpdating = False Application.DisplayAlerts = False shEmail = "eMail List" 'sheet where eMails are listed CurrentRow = 1 EndOfFile = Worksheets(shEmail).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row GetList = Worksheets(shEmail).Range("A" & CurrentRow) While CurrentRow < EndOfFile If CurrentRow > 1 Then GetList = GetList & ", " & Worksheets(shEmail).Range("A" & CurrentRow) CurrentRow = CurrentRow + 1 Wend Answer = MsgBox("You are about to send this file to " & GetList, vbOKCancel) If Answer = vbOK Then ActiveWorkbook.SendMail Recipients:=GetList Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub

mtrevino57
mtrevino57

What you had was not exactly what I needed but it did give me something to think about. I did get it to work. and it was way simpler using the VBA Split function. The actual code is Public Sub EmailSheet() Dim GetList As String Dim DistList As Variant Dim i As Long smResult = Worksheets("Email").Range("D24") 'D24 contains the Subject Line. GetList = Worksheets("Email").Range("F22") ' F22 contains the comma seperated list of recipients. DistList = Split(GetList, ",") 'Split Function seperates GetList using comma as seperator and stores each value into DistList element Answer = MsgBox("You are about to send this file to " & GetList & " Subject:" & smResult, vbOKCancel) If Answer = vbOK Then ActiveWorkbook.SendMail Recipients:=DistList, Subject:=smResult End Sub And it works just the way I needed. Now I can send worksheets to any number of recipients included on the worksheet.

mtrevino57
mtrevino57

Apparently SendMail Object does not like e-mail as a list, and it has to be stored in an array variable for Excel's Sendmail Object to work. I already have the recipients collected into a comma seperated list and stored that in a single cell. I did this because I want to be able to include and exclude recipients, so with the names being in a column, there will be blanks for those whom I do NOT want to send the worksheet to. This is why I did the concatenation on the worksheet and stored the final email "list" in a single cell. What I need to be able to do now is take cell content(the email list) and parse that into an array variable which the Sendmail object does like to send the worksheet to everyone on the list.