Automatic Email of Workbooks

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
yes - set up a cell which contains the client email address (as a formula if it's variable - (ie as a LOOKUP or something))

Then

ActiveWorkbook.SendMail Recipients:=range("the cell")
 

Scruffymessiah

New Member
Joined
Sep 5, 2002
Messages
45
Sorry about this, but i cant seem to get your method to work.... Can you explain it in a more fundemental way?
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Let's say in Sheet 1 Range A1 I have a formula that does a look up on the basis of supplier in Cell A2 and gets the appropriate email address

A1 = Vlookup(a2,suppliers,2,false)

and this has then returned

j.bloggs@bloggsville.com

My macro to send this would be as follows:

Sub MAILSPREADSHEET ()

ActiveWorkbook.SendMail recipients:= sheets("sheet1").range("a1")

End Sub
 

Bugs

Board Regular
Joined
Aug 21, 2002
Messages
100

ADVERTISEMENT

Try this
Sub Email()
ActiveWorkbook.SendMail Recipients:="XXXXX.com"
End Sub
 

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
I believe something like this is what he means....("a4") would be the cell that your e-mail address is in that you want the workbook sent to...or you could hide it way over in cell "aa4" or anywhere...copy and paste this into a module in your workbook.

Sub sendmail()
ActiveWorkbook.sendmail Recipients:=Range("a4")

End Sub



Then you need to put this in your "sheet1"

Private Sub CommandButton1_Click()
sendmail
End Sub

This of course is assuming you created a command button on your worksheet...and then put your e-mail address in cell a4 in sheet1 to test it out...

Sorry if this isn't very clear, I'm not very good at explaining....:-/
 

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
I believe something like this is what he means....("a4") would be the cell that your e-mail address is in that you want the workbook sent to...or you could hide it way over in cell "aa4" or anywhere...copy and paste this into a module in your workbook.

Sub sendmail()
ActiveWorkbook.sendmail Recipients:=Range("a4")

End Sub



Then you need to put this in your "sheet1"

Private Sub CommandButton1_Click()
sendmail
End Sub

This of course is assuming you created a command button on your worksheet...and then put your e-mail address in cell a4 in sheet1 to test it out...

Sorry if this isn't very clear, I'm not very good at explaining....:-/
 

Forum statistics

Threads
1,143,709
Messages
5,720,389
Members
422,282
Latest member
psunith

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
Top