Communicating with Outlook

[Matt Jensen]

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").Value
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").Value =
False
End Sub

 

[Ron de Bruin]

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

 

[Matt Jensen]

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]

Hi Matt

My answer is about this

Post your question in a Outlook Group.
You get better answer there

 

[Matt Jensen]

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