Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Communicating with Outlook
I have similar to the following code, I've cut out a large section for
simplicity's sake. Just wondering, is it possible, at the bottom of my code, to 'learn' from Outlook whether the email composed was actually sent or not? If so, I can confidently set the flag on the last line of my sub to False which I can't currently, just have to assume. If it's not sent then I don't want to flag as false obviously but true. TIA CHeers Matt Option Explicit Sub A10SendEmail() 'dimension variables Dim vaDetailChanges As Variant Dim vaAuthorisationChanges As Variant Dim i As Integer Dim wb As Workbook Dim boolDetailChanges, boolNewAuthorisations As Boolean Dim strEmail, strProjectName, strFromName As String Set wb = ActiveWorkbook wb.Save 'my code vaDetailChanges = Worksheets("Data-DetailsUpdates").UsedRange.Value 'Get variables to put in email fields strEmail = Worksheets("Data-Application").Range("projectvar_DLPMOEmail").Value strProjectName = Worksheets("Data-ProjectDetails").Range("projectvar_ProjectName").V alue strFromName = Worksheets("Data-ProjectDetails").Range("projectvar_PM").Value ' Outlook Automation ' Start Outlook - existing instance will be used if it is already running Dim olApp As Object 'Outlook.Application Set olApp = CreateObject("Outlook.Application") 'Create an email Dim olMail As Object 'Outlook.MailItem 'Set olMail = olApp.createitem(olMail) Set olMail = olApp.createitem(0) 'set email recipient On Error Resume Next 'if strEmail is blank we'll get an error so this allows for that olMail.To = strEmail 'create base part of HTML email body olMail.HTMLBody = _ "Dear PMO,<br /" 'create next part of body based on whether there were any updates to report (to allow for email send when not explicitly required) 'Add any Project Details updates 'Add any new Project Authorisations 'set closing part of email olMail.HTMLBody = olMail.HTMLBody & _ "Regards,<br /" & strFromName & "<br / " 'add checklist as attachment olMail.Attachments.Add wb.FullName, 1, 1, wb.Name 'display new email ready to be sent olMail.Display 'could use olMail.Send to send without user seeing it, but not suitable currently (and asks user if Email program can be accessed). 'Clean up Set olMail = Nothing Set olApp = Nothing Worksheets("Data-Application").Range("appvar_EmailRecommended").Val ue = False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Communicating with Outlook
Hi Matt
Use Send and read this http://www.rondebruin.nl/mail/prevent.htm For more mailing examples see http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Matt Jensen" wrote in message ... I have similar to the following code, I've cut out a large section for simplicity's sake. Just wondering, is it possible, at the bottom of my code, to 'learn' from Outlook whether the email composed was actually sent or not? If so, I can confidently set the flag on the last line of my sub to False which I can't currently, just have to assume. If it's not sent then I don't want to flag as false obviously but true. TIA CHeers Matt Option Explicit Sub A10SendEmail() 'dimension variables Dim vaDetailChanges As Variant Dim vaAuthorisationChanges As Variant Dim i As Integer Dim wb As Workbook Dim boolDetailChanges, boolNewAuthorisations As Boolean Dim strEmail, strProjectName, strFromName As String Set wb = ActiveWorkbook wb.Save 'my code vaDetailChanges = Worksheets("Data-DetailsUpdates").UsedRange.Value 'Get variables to put in email fields strEmail = Worksheets("Data-Application").Range("projectvar_DLPMOEmail").Value strProjectName = Worksheets("Data-ProjectDetails").Range("projectvar_ProjectName").V alue strFromName = Worksheets("Data-ProjectDetails").Range("projectvar_PM").Value ' Outlook Automation ' Start Outlook - existing instance will be used if it is already running Dim olApp As Object 'Outlook.Application Set olApp = CreateObject("Outlook.Application") 'Create an email Dim olMail As Object 'Outlook.MailItem 'Set olMail = olApp.createitem(olMail) Set olMail = olApp.createitem(0) 'set email recipient On Error Resume Next 'if strEmail is blank we'll get an error so this allows for that olMail.To = strEmail 'create base part of HTML email body olMail.HTMLBody = _ "Dear PMO,<br /" 'create next part of body based on whether there were any updates to report (to allow for email send when not explicitly required) 'Add any Project Details updates 'Add any new Project Authorisations 'set closing part of email olMail.HTMLBody = olMail.HTMLBody & _ "Regards,<br /" & strFromName & "<br / " 'add checklist as attachment olMail.Attachments.Add wb.FullName, 1, 1, wb.Name 'display new email ready to be sent olMail.Display 'could use olMail.Send to send without user seeing it, but not suitable currently (and asks user if Email program can be accessed). 'Clean up Set olMail = Nothing Set olApp = Nothing Worksheets("Data-Application").Range("appvar_EmailRecommended").Val ue = False End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Communicating with Outlook
Thanks Ron
I don't know that that will solve my situation though. Apart from being in no position to rollout COM objects to 100s of workstations that are locked down unfortunately, I also WANT people to have the option of EDITING the email before it is sent as well as allowing them to NOT SEND it if that's what they want (for whatever reason), I just was hoping to get some confirmation back to Excel/VB from Outlook as to whether they did send it or not. Did I miss any example because I didn't see anything about that at those links. Any further ideas? Thanks in advance Matt "Ron de Bruin" wrote in message ... Hi Matt Use Send and read this http://www.rondebruin.nl/mail/prevent.htm For more mailing examples see http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Matt Jensen" wrote in message ... I have similar to the following code, I've cut out a large section for simplicity's sake. Just wondering, is it possible, at the bottom of my code, to 'learn' from Outlook whether the email composed was actually sent or not? If so, I can confidently set the flag on the last line of my sub to False which I can't currently, just have to assume. If it's not sent then I don't want to flag as false obviously but true. TIA CHeers Matt Option Explicit Sub A10SendEmail() 'dimension variables Dim vaDetailChanges As Variant Dim vaAuthorisationChanges As Variant Dim i As Integer Dim wb As Workbook Dim boolDetailChanges, boolNewAuthorisations As Boolean Dim strEmail, strProjectName, strFromName As String Set wb = ActiveWorkbook wb.Save 'my code vaDetailChanges = Worksheets("Data-DetailsUpdates").UsedRange.Value 'Get variables to put in email fields strEmail = Worksheets("Data-Application").Range("projectvar_DLPMOEmail").Value strProjectName = Worksheets("Data-ProjectDetails").Range("projectvar_ProjectName").V alue strFromName = Worksheets("Data-ProjectDetails").Range("projectvar_PM").Value ' Outlook Automation ' Start Outlook - existing instance will be used if it is already running Dim olApp As Object 'Outlook.Application Set olApp = CreateObject("Outlook.Application") 'Create an email Dim olMail As Object 'Outlook.MailItem 'Set olMail = olApp.createitem(olMail) Set olMail = olApp.createitem(0) 'set email recipient On Error Resume Next 'if strEmail is blank we'll get an error so this allows for that olMail.To = strEmail 'create base part of HTML email body olMail.HTMLBody = _ "Dear PMO,<br /" 'create next part of body based on whether there were any updates to report (to allow for email send when not explicitly required) 'Add any Project Details updates 'Add any new Project Authorisations 'set closing part of email olMail.HTMLBody = olMail.HTMLBody & _ "Regards,<br /" & strFromName & "<br / " 'add checklist as attachment olMail.Attachments.Add wb.FullName, 1, 1, wb.Name 'display new email ready to be sent olMail.Display 'could use olMail.Send to send without user seeing it, but not suitable currently (and asks user if Email program can be accessed). 'Clean up Set olMail = Nothing Set olApp = Nothing Worksheets("Data-Application").Range("appvar_EmailRecommended").Val ue = False End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Communicating with Outlook
Hi Matt
olMail.Display 'could use olMail.Send to send without user seeing it, but not suitable currently (and asks user if Email program can be accessed). My answer is about this Post your question in a Outlook Group. You get better answer there -- Regards Ron de Bruin http://www.rondebruin.nl "Matt Jensen" wrote in message ... Thanks Ron I don't know that that will solve my situation though. Apart from being in no position to rollout COM objects to 100s of workstations that are locked down unfortunately, I also WANT people to have the option of EDITING the email before it is sent as well as allowing them to NOT SEND it if that's what they want (for whatever reason), I just was hoping to get some confirmation back to Excel/VB from Outlook as to whether they did send it or not. Did I miss any example because I didn't see anything about that at those links. Any further ideas? Thanks in advance Matt "Ron de Bruin" wrote in message ... Hi Matt Use Send and read this http://www.rondebruin.nl/mail/prevent.htm For more mailing examples see http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Matt Jensen" wrote in message ... I have similar to the following code, I've cut out a large section for simplicity's sake. Just wondering, is it possible, at the bottom of my code, to 'learn' from Outlook whether the email composed was actually sent or not? If so, I can confidently set the flag on the last line of my sub to False which I can't currently, just have to assume. If it's not sent then I don't want to flag as false obviously but true. TIA CHeers Matt Option Explicit Sub A10SendEmail() 'dimension variables Dim vaDetailChanges As Variant Dim vaAuthorisationChanges As Variant Dim i As Integer Dim wb As Workbook Dim boolDetailChanges, boolNewAuthorisations As Boolean Dim strEmail, strProjectName, strFromName As String Set wb = ActiveWorkbook wb.Save 'my code vaDetailChanges = Worksheets("Data-DetailsUpdates").UsedRange.Value 'Get variables to put in email fields strEmail = Worksheets("Data-Application").Range("projectvar_DLPMOEmail").Value strProjectName = Worksheets("Data-ProjectDetails").Range("projectvar_ProjectName").V alue strFromName = Worksheets("Data-ProjectDetails").Range("projectvar_PM").Value ' Outlook Automation ' Start Outlook - existing instance will be used if it is already running Dim olApp As Object 'Outlook.Application Set olApp = CreateObject("Outlook.Application") 'Create an email Dim olMail As Object 'Outlook.MailItem 'Set olMail = olApp.createitem(olMail) Set olMail = olApp.createitem(0) 'set email recipient On Error Resume Next 'if strEmail is blank we'll get an error so this allows for that olMail.To = strEmail 'create base part of HTML email body olMail.HTMLBody = _ "Dear PMO,<br /" 'create next part of body based on whether there were any updates to report (to allow for email send when not explicitly required) 'Add any Project Details updates 'Add any new Project Authorisations 'set closing part of email olMail.HTMLBody = olMail.HTMLBody & _ "Regards,<br /" & strFromName & "<br / " 'add checklist as attachment olMail.Attachments.Add wb.FullName, 1, 1, wb.Name 'display new email ready to be sent olMail.Display 'could use olMail.Send to send without user seeing it, but not suitable currently (and asks user if Email program can be accessed). 'Clean up Set olMail = Nothing Set olApp = Nothing Worksheets("Data-Application").Range("appvar_EmailRecommended").Val ue = False End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Communicating with Outlook
Thanks Ron - have posted in Outlook group.
Obviously then there is no simple method of the Outlook object that tells my Excel/VBA app whether the email it created was sent then...? Cheers Matt "Ron de Bruin" wrote in message ... Hi Matt olMail.Display 'could use olMail.Send to send without user seeing it, but not suitable currently (and asks user if Email program can be accessed). My answer is about this Post your question in a Outlook Group. You get better answer there -- Regards Ron de Bruin http://www.rondebruin.nl "Matt Jensen" wrote in message ... Thanks Ron I don't know that that will solve my situation though. Apart from being in no position to rollout COM objects to 100s of workstations that are locked down unfortunately, I also WANT people to have the option of EDITING the email before it is sent as well as allowing them to NOT SEND it if that's what they want (for whatever reason), I just was hoping to get some confirmation back to Excel/VB from Outlook as to whether they did send it or not. Did I miss any example because I didn't see anything about that at those links. Any further ideas? Thanks in advance Matt "Ron de Bruin" wrote in message ... Hi Matt Use Send and read this http://www.rondebruin.nl/mail/prevent.htm For more mailing examples see http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Matt Jensen" wrote in message ... I have similar to the following code, I've cut out a large section for simplicity's sake. Just wondering, is it possible, at the bottom of my code, to 'learn' from Outlook whether the email composed was actually sent or not? If so, I can confidently set the flag on the last line of my sub to False which I can't currently, just have to assume. If it's not sent then I don't want to flag as false obviously but true. TIA CHeers Matt Option Explicit Sub A10SendEmail() 'dimension variables Dim vaDetailChanges As Variant Dim vaAuthorisationChanges As Variant Dim i As Integer Dim wb As Workbook Dim boolDetailChanges, boolNewAuthorisations As Boolean Dim strEmail, strProjectName, strFromName As String Set wb = ActiveWorkbook wb.Save 'my code vaDetailChanges = Worksheets("Data-DetailsUpdates").UsedRange.Value 'Get variables to put in email fields strEmail = Worksheets("Data-Application").Range("projectvar_DLPMOEmail").Value strProjectName = Worksheets("Data-ProjectDetails").Range("projectvar_ProjectName").V alue strFromName = Worksheets("Data-ProjectDetails").Range("projectvar_PM").Value ' Outlook Automation ' Start Outlook - existing instance will be used if it is already running Dim olApp As Object 'Outlook.Application Set olApp = CreateObject("Outlook.Application") 'Create an email Dim olMail As Object 'Outlook.MailItem 'Set olMail = olApp.createitem(olMail) Set olMail = olApp.createitem(0) 'set email recipient On Error Resume Next 'if strEmail is blank we'll get an error so this allows for that olMail.To = strEmail 'create base part of HTML email body olMail.HTMLBody = _ "Dear PMO,<br /" 'create next part of body based on whether there were any updates to report (to allow for email send when not explicitly required) 'Add any Project Details updates 'Add any new Project Authorisations 'set closing part of email olMail.HTMLBody = olMail.HTMLBody & _ "Regards,<br /" & strFromName & "<br / " 'add checklist as attachment olMail.Attachments.Add wb.FullName, 1, 1, wb.Name 'display new email ready to be sent olMail.Display 'could use olMail.Send to send without user seeing it, but not suitable currently (and asks user if Email program can be accessed). 'Clean up Set olMail = Nothing Set olApp = Nothing Worksheets("Data-Application").Range("appvar_EmailRecommended").Val ue = False End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Outlook 11 Outlook 10 Object Library Compatibility Issues | Excel Programming | |||
Late Binding to Outlook from Excel: Outlook modifies email body | Excel Programming | |||
Communicating down RS232 | Excel Programming | |||
Communicating errors from a custom function in an add-in | Excel Programming | |||
HELP - Communicating between C++ and VBA | Excel Programming |