Automatic Email of Workbooks

[Scruffymessiah]

Is it possible to set up a button that will email the workbook to a specific client?

 

[DonkeyOte]

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]

Sorry about this, but i cant seem to get your method to work.... Can you explain it in a more fundemental way?

 

[DonkeyOte]

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]

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

 

[Davers]

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]

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....:-/

 

[Scruffymessiah]

Thanks. I'll try not to bother you again.