ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Sendmail with a named cell value as recipient (https://www.excelbanter.com/excel-programming/392548-sendmail-named-cell-value-recipient.html)

ssGuru

Sendmail with a named cell value as recipient
 
Have been working up an app and need to send emails from it.
I have reviewed Ron de Bruin's excellent articles and have configured
code to work.

Using Sendmail I can happily email colleagues if I use the prescribed
syntax:

Activeworkbook.Sendmail Recipient, Subject

Sooooooo,
Activeworkbook.Sendmail ", "Forcast " & Format(Now, "mm-dd-
yy")
After a dialogue warning asking permisssion about sending the mail it
works just fine with Outlook.

However I want to use the recipient name based on the value in a named
cell in the workbook
For example a cell NAMED "MgrEMail" with the value
as the
current value

I have tried the obvious .SendMail MgrEMail, "Forcast " & Format(Now,
"mm-dd-yy") but it just opens Outlook without a recipient.

Is there a way to do this?

Thanks,
Dennis


Mike

Sendmail with a named cell value as recipient
 
Private Sub sendSheet()
Dim eMailAddress As String

eMailAddress = Range("MgrEMail").Value

ActiveWorkbook.SendMail _
Recipients:=eMailAddress, _
Subject:="Forcast " & Format(Date, "mm-dd-yy ")


End Sub

"ssGuru" wrote:

Have been working up an app and need to send emails from it.
I have reviewed Ron de Bruin's excellent articles and have configured
code to work.

Using Sendmail I can happily email colleagues if I use the prescribed
syntax:

Activeworkbook.Sendmail Recipient, Subject

Sooooooo,
Activeworkbook.Sendmail ", "Forcast " & Format(Now, "mm-dd-
yy")
After a dialogue warning asking permisssion about sending the mail it
works just fine with Outlook.

However I want to use the recipient name based on the value in a named
cell in the workbook
For example a cell NAMED "MgrEMail" with the value
as the
current value

I have tried the obvious .SendMail MgrEMail, "Forcast " & Format(Now,
"mm-dd-yy") but it just opens Outlook without a recipient.

Is there a way to do this?

Thanks,
Dennis



ssGuru

Sendmail with a named cell value as recipient
 
On Jul 2, 6:44 pm, Mike wrote:
Private Sub sendSheet()
Dim eMailAddress As String

eMailAddress = Range("MgrEMail").Value

ActiveWorkbook.SendMail _
Recipients:=eMailAddress, _
Subject:="Forcast " & Format(Date, "mm-dd-yy ")

End Sub


Thanks Mike. It worked perfectly with Outlook. I've also added some
other ranges to the subject and attached file name using the same
code.
Any known problems with other eMail clients? Eudora, LotusNotes,
etc..
Dennis



All times are GMT +1. The time now is 09:09 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com