Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to email to address in cell reference | Excel Discussion (Misc queries) | |||
find email address in a cell | Excel Worksheet Functions | |||
How do I enter an email address w/in a cell & it not be automatic | Excel Discussion (Misc queries) | |||
Excel Email: Address and portion of body in cell | Excel Programming | |||
How to format a cell as email address | Excel Discussion (Misc queries) |