Hello.
I have made a VBA Macro in Excel to automate the monthly mail distribution to our customers. The macro scans through our excel-based customer list and generates outlook mail items based on this list. This is working fine. The problem is that I want to be able to review every mail before sending it. I have tried this using the .display function, but then the macro keeps looping through the adress list leading to all 90 mails beeing created and shown simultaneously.
What I need to make the macro do is pause after displaying the mail item and waiting for the user to click "send" or to close the mail item. I have tried doing this by using Outlook Events and a loop, as seen in the code below. I'm a bit in over my head here as I am an economist not an IT-scholar, so I'm not quite sure how to handle the events, that part is more or less cut-and-paste from an example provided by MSDN.
The complete code is rather long but the bits concerned with the actual mail item looks like this:
Are there any bright minds out there that can help me make the macro wait at the spot of my (as it is now) never ending loop? It needn't be by using the method I have tried, any solution is welcome. I have been stuck with this problem for the best of two days now and I'm about to give up on the thing.
I have made a VBA Macro in Excel to automate the monthly mail distribution to our customers. The macro scans through our excel-based customer list and generates outlook mail items based on this list. This is working fine. The problem is that I want to be able to review every mail before sending it. I have tried this using the .display function, but then the macro keeps looping through the adress list leading to all 90 mails beeing created and shown simultaneously.
What I need to make the macro do is pause after displaying the mail item and waiting for the user to click "send" or to close the mail item. I have tried doing this by using Outlook Events and a loop, as seen in the code below. I'm a bit in over my head here as I am an economist not an IT-scholar, so I'm not quite sure how to handle the events, that part is more or less cut-and-paste from an example provided by MSDN.
The complete code is rather long but the bits concerned with the actual mail item looks like this:
Public WithEvents OutMail As Outlook.MailItem
Dim MailSent As Boolean
Sub maildistribution
(...)
'Declearing and setting variables
Dim OutApp As Object
Set OutApp = CreateObject("Outlook.Application")
With Application
.ScreenUpdating = False
End With
MailSent=False
(...)
'Creating the mail item
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
Set OutMailInspector = OutMail.GetInspector
With OutMail
.To = Til
.CC = CC
.SentOnBehalfOfName = Fra
.Subject = Emne & Navn
.Body = Melding & ManedNavn & "." & Signatur
.display
End With
Do while MailSent = False
Loop
(...)
End sub
Public Sub OutMail_Send(Cancel As Boolean)
MailSent = True
End Sub
However it seems that pressing the 'send' button in the Outlook Mail Item 'OutMail' does not fire the OutMail_Send routine, leading to MailSent being False and the loop (marked in red) to go on forever.Dim MailSent As Boolean
Sub maildistribution
(...)
'Declearing and setting variables
Dim OutApp As Object
Set OutApp = CreateObject("Outlook.Application")
With Application
.ScreenUpdating = False
End With
MailSent=False
(...)
'Creating the mail item
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
Set OutMailInspector = OutMail.GetInspector
With OutMail
.To = Til
.CC = CC
.SentOnBehalfOfName = Fra
.Subject = Emne & Navn
.Body = Melding & ManedNavn & "." & Signatur
.display
End With
Do while MailSent = False
Loop
(...)
End sub
Public Sub OutMail_Send(Cancel As Boolean)
MailSent = True
End Sub
Are there any bright minds out there that can help me make the macro wait at the spot of my (as it is now) never ending loop? It needn't be by using the method I have tried, any solution is welcome. I have been stuck with this problem for the best of two days now and I'm about to give up on the thing.
Last edited: