Return notification to Excel VBA that Email sent

[Matt Jensen]

Howdy
Developing an Excel/VBA app that creates and displays a new Outlook email
with the Excel workbook attached and I need to allow the user to edit the
body of the email.
I was wondering if there's any simple method of the Outlook object that I
can use to notify my Excel workbook that this email created and displayed
was actually sent?

My (cut-down) code looks like this:

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
olMail.To = strEmail

'create base part of HTML email body
olMail.HTMLBody = _
"Dear PMO,<br />"

'create body of email
' my code here

'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

'Clean up
Set olMail = Nothing
Set olApp = Nothing

'THIS IS THE FLAG VARIABLE USED FOR WHETHER EMAIL WAS ACTUALLY SENT
'Currently I assumes that running this sub means the email was sent, but
would like to know for sure if possible...?
Worksheets("Data-Application").Range("appvar_EmailRecommended").Value =
False

End Sub


Thanks for any help
Cheers
Matt

 

[Michael Bauer]

Hi Matt,

for a single mail item you could declare the variable WithEvents in the
modul head, this allows you receiving events from that item:

Private WithEvents olMail As Outlook.MailItem

Sub A10SendEmail()
' [...]
' You must not set the variable to nothing here!
' Set olMail = Nothing
End Sub

Private Sub olMail_Send(Cancel As Boolean)
' This code will be executed when the item was sent
' [...]
' No you could set olMail=Nothing
End Sub

If you need to monitor more than one item then you need a variable
declared WithEvents for each item. A sample I´ve posted yesterday in
this group, subject: "How to automatically show an email when it
arrives?" It´s a different variable and event, but it´s the same
mechanism.


--
Viele Grüße
Michael Bauer

Howdy
Developing an Excel/VBA app that creates and displays a new Outlook email
with the Excel workbook attached and I need to allow the user to edit the
body of the email.
I was wondering if there's any simple method of the Outlook object that I
can use to notify my Excel workbook that this email created and displayed
was actually sent?

My (cut-down) code looks like this:

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
olMail.To = strEmail

'create base part of HTML email body
olMail.HTMLBody = _
"Dear PMO,<br />"

'create body of email
' my code here

'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

'Clean up
Set olMail = Nothing
Set olApp = Nothing

'THIS IS THE FLAG VARIABLE USED FOR WHETHER EMAIL WAS ACTUALLY SENT
'Currently I assumes that running this sub means the email was sent, but
would like to know for sure if possible...?

Worksheets("Data-Application").Range("appvar_EmailRecommended").Value =

 

[Matt Jensen]

Sounds interesting thanks Michael
I'll check it out
Cheers
Matt

Hi Matt,

for a single mail item you could declare the variable WithEvents in the
modul head, this allows you receiving events from that item:

Private WithEvents olMail As Outlook.MailItem

Sub A10SendEmail()
' [...]
' You must not set the variable to nothing here!
' Set olMail = Nothing
End Sub

Private Sub olMail_Send(Cancel As Boolean)
' This code will be executed when the item was sent
' [...]
' No you could set olMail=Nothing
End Sub

If you need to monitor more than one item then you need a variable
declared WithEvents for each item. A sample I´ve posted yesterday in
this group, subject: "How to automatically show an email when it
arrives?" It´s a different variable and event, but it´s the same
mechanism.


--
Viele Grüße
Michael Bauer

Howdy
Developing an Excel/VBA app that creates and displays a new Outlook email
with the Excel workbook attached and I need to allow the user to edit the
body of the email.
I was wondering if there's any simple method of the Outlook object that I
can use to notify my Excel workbook that this email created and displayed
was actually sent?

My (cut-down) code looks like this:

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
olMail.To = strEmail

'create base part of HTML email body
olMail.HTMLBody = _
"Dear PMO,<br />"

'create body of email
' my code here

'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

'Clean up
Set olMail = Nothing
Set olApp = Nothing

'THIS IS THE FLAG VARIABLE USED FOR WHETHER EMAIL WAS ACTUALLY SENT
'Currently I assumes that running this sub means the email was sent, but
would like to know for sure if possible...?

Worksheets("Data-Application").Range("appvar_EmailRecommended").Value =

False

End Sub


Thanks for any help
Cheers
Matt

 

[VilMarci]

Hi,

I also need a mail check like this... How shall this .Send property used?
I use Outlook & Accees 2003.
According to the help it's a simple boolean, so I don't see why my code
isn't working:

.....
otlMail.Display

If otlMail.Sent = True Then 'why is it always False?

strMessage = "Message SENT to: " & strNev
MsgBox (strMessage)

Else
strMessage = "Message NOT SENT to: " '& strNev
MsgBox (strMessage)

Thanks for your help

Marton

Sounds interesting thanks Michael
I'll check it out
Cheers
Matt

Hi Matt,

for a single mail item you could declare the variable WithEvents in the
modul head, this allows you receiving events from that item:

Private WithEvents olMail As Outlook.MailItem

Sub A10SendEmail()
' [...]
' You must not set the variable to nothing here!
' Set olMail = Nothing
End Sub

Private Sub olMail_Send(Cancel As Boolean)
' This code will be executed when the item was sent
' [...]
' No you could set olMail=Nothing
End Sub

If you need to monitor more than one item then you need a variable
declared WithEvents for each item. A sample I´ve posted yesterday in
this group, subject: "How to automatically show an email when it
arrives?" It´s a different variable and event, but it´s the same
mechanism.


--
Viele Grüße
Michael Bauer

Howdy
Developing an Excel/VBA app that creates and displays a new Outlook email
with the Excel workbook attached and I need to allow the user to edit the
body of the email.
I was wondering if there's any simple method of the Outlook object that I
can use to notify my Excel workbook that this email created and displayed
was actually sent?

My (cut-down) code looks like this:

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
olMail.To = strEmail

'create base part of HTML email body
olMail.HTMLBody = _
"Dear PMO,<br />"

'create body of email
' my code here

'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

'Clean up
Set olMail = Nothing
Set olApp = Nothing

'THIS IS THE FLAG VARIABLE USED FOR WHETHER EMAIL WAS ACTUALLY SENT
'Currently I assumes that running this sub means the email was sent, but
would like to know for sure if possible...?

Worksheets("Data-Application").Range("appvar_EmailRecommended").Value =

False

End Sub


Thanks for any help
Cheers
Matt