ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   EMail to an address in a cell (https://www.excelbanter.com/excel-programming/419104-email-address-cell.html)

jatman
EMail to an address in a cell
 
good afternoon,

i have the followign macro:

Sub Mail_ActiveSheet_PDF_Outlook()
'Note: It is easy to change the code to send a workbook, selection or range.
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim FilenameStr As String

FilenameStr = "C:\Purchase Orders\" & _
Format(Now, "yyyy-mm-dd, ") & "PO# " & Range("M5").Value & ", " &
Range("H5").Value & ".pdf"

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=FilenameStr, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = ""

On Error Resume Next
With OutMail
.To = "
.Subject = "PO# " & Range("M5").Value & ", " & Range("H5").Value
.Body = strbody
.Attachments.Add FilenameStr
.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


everything works fine, except i want to add a CC recipient as follows:

there are two cells were an email address may be (D9 and H9). these cells
could be blank, have a "0" value because there is no email address to
reference or have an email address. if there is an email address in either
of the two fields, then they will also receive the email, otherwise, it will
only got to the defaul recipient in the macro.

can those cells be referenced as value... range("d9").value?

jat

Ron de Bruin
EMail to an address in a cell
 
Try it like this

Sub Mail_ActiveSheet_PDF_Outlook()
'Note: It is easy to change the code to send a workbook, selection or range.
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim FilenameStr As String
Dim cell As Range
Dim strto As String

For Each cell In Range("D9,H9")
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next cell
If Len(strto) 0 Then strto = Left(strto, Len(strto) - 1)

FilenameStr = "C:\Purchase Orders\" & _
Format(Now, "yyyy-mm-dd, ") & "PO# " & Range("M5").Value & ", " & Range("H5").Value & ".pdf"

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=FilenameStr, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = ""

On Error Resume Next
With OutMail
.To = "
.CC = strto
.Subject = "PO# " & Range("M5").Value & ", " & Range("H5").Value
.Body = strbody
.Attachments.Add FilenameStr
.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"jatman" wrote in message ...
good afternoon,

i have the followign macro:

Sub Mail_ActiveSheet_PDF_Outlook()
'Note: It is easy to change the code to send a workbook, selection or range.
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim FilenameStr As String

FilenameStr = "C:\Purchase Orders\" & _
Format(Now, "yyyy-mm-dd, ") & "PO# " & Range("M5").Value & ", " &
Range("H5").Value & ".pdf"

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=FilenameStr, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = ""

On Error Resume Next
With OutMail
.To = "
.Subject = "PO# " & Range("M5").Value & ", " & Range("H5").Value
.Body = strbody
.Attachments.Add FilenameStr
.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


everything works fine, except i want to add a CC recipient as follows:

there are two cells were an email address may be (D9 and H9). these cells
could be blank, have a "0" value because there is no email address to
reference or have an email address. if there is an email address in either
of the two fields, then they will also receive the email, otherwise, it will
only got to the defaul recipient in the macro.

can those cells be referenced as value... range("d9").value?

jat

Larry S
EMail to an address in a cell
 
Ron, how would the code read if you wanted outlook to open so that the email
could be sent manually?

Larry

"Ron de Bruin" wrote:

Try it like this

Sub Mail_ActiveSheet_PDF_Outlook()
'Note: It is easy to change the code to send a workbook, selection or range.
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim FilenameStr As String
Dim cell As Range
Dim strto As String

For Each cell In Range("D9,H9")
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next cell
If Len(strto) 0 Then strto = Left(strto, Len(strto) - 1)

FilenameStr = "C:\Purchase Orders\" & _
Format(Now, "yyyy-mm-dd, ") & "PO# " & Range("M5").Value & ", " & Range("H5").Value & ".pdf"

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=FilenameStr, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = ""

On Error Resume Next
With OutMail
.To = "
.CC = strto
.Subject = "PO# " & Range("M5").Value & ", " & Range("H5").Value
.Body = strbody
.Attachments.Add FilenameStr
.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"jatman" wrote in message ...
good afternoon,

i have the followign macro:

Sub Mail_ActiveSheet_PDF_Outlook()
'Note: It is easy to change the code to send a workbook, selection or range.
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim FilenameStr As String

FilenameStr = "C:\Purchase Orders\" & _
Format(Now, "yyyy-mm-dd, ") & "PO# " & Range("M5").Value & ", " &
Range("H5").Value & ".pdf"

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=FilenameStr, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = ""

On Error Resume Next
With OutMail
.To = "
.Subject = "PO# " & Range("M5").Value & ", " & Range("H5").Value
.Body = strbody
.Attachments.Add FilenameStr
.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


everything works fine, except i want to add a CC recipient as follows:

there are two cells were an email address may be (D9 and H9). these cells
could be blank, have a "0" value because there is no email address to
reference or have an email address. if there is an email address in either
of the two fields, then they will also receive the email, otherwise, it will
only got to the defaul recipient in the macro.

can those cells be referenced as value... range("d9").value?

jat

Ron de Bruin
EMail to an address in a cell
 
Hi Larry

Change

.Send


To

..Display


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Larry S" wrote in message ...
Ron, how would the code read if you wanted outlook to open so that the email
could be sent manually?

Larry

"Ron de Bruin" wrote:

Try it like this

Sub Mail_ActiveSheet_PDF_Outlook()
'Note: It is easy to change the code to send a workbook, selection or range.
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim FilenameStr As String
Dim cell As Range
Dim strto As String

For Each cell In Range("D9,H9")
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next cell
If Len(strto) 0 Then strto = Left(strto, Len(strto) - 1)

FilenameStr = "C:\Purchase Orders\" & _
Format(Now, "yyyy-mm-dd, ") & "PO# " & Range("M5").Value & ", " & Range("H5").Value & ".pdf"

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=FilenameStr, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = ""

On Error Resume Next
With OutMail
.To = "
.CC = strto
.Subject = "PO# " & Range("M5").Value & ", " & Range("H5").Value
.Body = strbody
.Attachments.Add FilenameStr
.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"jatman" wrote in message ...
good afternoon,

i have the followign macro:

Sub Mail_ActiveSheet_PDF_Outlook()
'Note: It is easy to change the code to send a workbook, selection or range.
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim FilenameStr As String

FilenameStr = "C:\Purchase Orders\" & _
Format(Now, "yyyy-mm-dd, ") & "PO# " & Range("M5").Value & ", " &
Range("H5").Value & ".pdf"

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=FilenameStr, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = ""

On Error Resume Next
With OutMail
.To = "
.Subject = "PO# " & Range("M5").Value & ", " & Range("H5").Value
.Body = strbody
.Attachments.Add FilenameStr
.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


everything works fine, except i want to add a CC recipient as follows:

there are two cells were an email address may be (D9 and H9). these cells
could be blank, have a "0" value because there is no email address to
reference or have an email address. if there is an email address in either
of the two fields, then they will also receive the email, otherwise, it will
only got to the defaul recipient in the macro.

can those cells be referenced as value... range("d9").value?

jat

Larry S
EMail to an address in a cell
 
i just found that in an earlier post of yours.
thanks for the speedy resonse though.

Larry


"Ron de Bruin" wrote:

Hi Larry

Change

.Send


To

..Display


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Larry S" wrote in message ...
Ron, how would the code read if you wanted outlook to open so that the email
could be sent manually?

Larry

"Ron de Bruin" wrote:

Try it like this

Sub Mail_ActiveSheet_PDF_Outlook()
'Note: It is easy to change the code to send a workbook, selection or range.
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim FilenameStr As String
Dim cell As Range
Dim strto As String

For Each cell In Range("D9,H9")
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next cell
If Len(strto) 0 Then strto = Left(strto, Len(strto) - 1)

FilenameStr = "C:\Purchase Orders\" & _
Format(Now, "yyyy-mm-dd, ") & "PO# " & Range("M5").Value & ", " & Range("H5").Value & ".pdf"

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=FilenameStr, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = ""

On Error Resume Next
With OutMail
.To = "
.CC = strto
.Subject = "PO# " & Range("M5").Value & ", " & Range("H5").Value
.Body = strbody
.Attachments.Add FilenameStr
.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"jatman" wrote in message ...
good afternoon,

i have the followign macro:

Sub Mail_ActiveSheet_PDF_Outlook()
'Note: It is easy to change the code to send a workbook, selection or range.
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim FilenameStr As String

FilenameStr = "C:\Purchase Orders\" & _
Format(Now, "yyyy-mm-dd, ") & "PO# " & Range("M5").Value & ", " &
Range("H5").Value & ".pdf"

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=FilenameStr, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = ""

On Error Resume Next
With OutMail
.To = "
.Subject = "PO# " & Range("M5").Value & ", " & Range("H5").Value
.Body = strbody
.Attachments.Add FilenameStr
.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


everything works fine, except i want to add a CC recipient as follows:

there are two cells were an email address may be (D9 and H9). these cells
could be blank, have a "0" value because there is no email address to
reference or have an email address. if there is an email address in either
of the two fields, then they will also receive the email, otherwise, it will
only got to the defaul recipient in the macro.

can those cells be referenced as value... range("d9").value?

jat


All times are GMT +1. The time now is 10:49 AM.

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