Trouble using Outlook Automation

MR Campbell

Board Regular
Joined
Aug 2, 2002
Messages
113
I am trying to get my Excel macro (code shown below) to automate Outlook by sending some emails in a loop but invariably in the middle of running this, the programme hangs. I have no idea why this happens.

When this technique is used, I am not sure whether I am supposed to have Outlook running already or not. When the Outlook application object is used does it matter whether Outlook is running or not ?

I would envisage the user clicks a button and emails are sent using Outlook using this macro, with the email addresses and other details are already stored with my Excel workbook.

Please help me !!


Private Sub SendMailviaOutlook()
'Sends a general email message to ALL Parents from the screen parameters via OUTLOOK


Dim OutlookApp As Outlook.Application 'outlook application object
Dim MailItem As Outlook.MailItem 'outlook mail object


Dim DOBRow As Integer 'row number in the DOB Squad sheet
Dim EmailsSent As Integer 'counts the number of emails sent

Dim Title As String 'Email title
Dim ReturnAdd As String 'Return email address
Dim Attach As String 'Attachment file and location

Dim TextBody As String 'main text for the Parent Email
Dim ParentSalutation As String 'parent saluatation e.g. Mr and Mrs SMITH
Dim EmailAddress As String 'parent email address

Dim Para1 As String 'paragraph 1 read from the Pem sheet
Dim Para2 As String 'paragraph 2 read from the Pem sheet
Dim Para3 As String 'paragraph 3 read from the Pem sheet
Dim SignOff As String 'sign off text e.g. Yours sincerely,
Dim SenderName As String 'personal name of the sender e.g. Grahame Lowe
Dim Position As String 'Position e.g. Head Coach
Dim SchoolName As String 'name of school e.g. Hale School

'Erase SENT emails message
Sheets("Pem").Range("j30").Value = ""

EmailsSent = 0 'initialise NO emails yet sent
DOBRow = 6 'first row to examine in the DOB squad sheet
SquadNum = Sheets("DOB").Range("TotalNum").Value 'number students in the squad

'Create Outlook object
'Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookApp = New Outlook.Application

'Read the parameters for each email
With Sheets("Pem")
Title = .Range("Title").Value 'email title
ReturnAdd = .Range("ReturnAdd").Value 'return email address
Attach = .Range("Attach").Value 'attachment file
Para1 = .Range("Para1").Value
Para2 = .Range("Para2").Value
Para3 = .Range("Para3").Value
SignOff = .Range("SignOff").Value
SenderName = .Range("SenderName").Value
Position = .Range("Position").Value
SchoolName = .Range("SchoolName").Value
End With

'Compose the main body of the Email
TextBody = Para1 & vbCrLf & vbCrLf & Para2 & vbCrLf & vbCrLf & Para3 & vbCrLf & vbCrLf & _
SignOff & vbCrLf & SenderName & vbCrLf & vbCrLf & Position & vbCrLf & SchoolName

'Read each row in the DOB Squad Sheet a
For DOBRow = 6 To SquadNum + 5
EmailAddress = Sheets("DOB").Cells(DOBRow, 30).Value 'parent email address in col 23

'Test if there is an email address
If EmailAddress <> "" Then
ParentSalutation = Sheets("DOB").Cells(DOBRow, 24).Value 'salutation

'Create mail item and send it
Set MailItem = OutlookApp.createitem(olmailitem)
With MailItem
.Subject = Title 'email title
.to = EmailAddress 'parent email
.Body = "Dear " & ParentSalutation & vbCrLf & vbCrLf & TextBody 'body of text
If Attach <> "" Then
.Attachment.Add Attach 'attachment if there is one
End If
.Send
End With

EmailsSent = EmailsSent + 1 'increase count sent by one
'Report the number of emails sent
With Sheets("Pem")
.Range("L29").Value = EmailsSent 'number of emails sent
End With

Set MailItem = Nothing 'kill the Parent email object

EmailAddress = Sheets("DOB").Cells(DOBRow, 31).Value '2nd parent email address col 26
'Test if there is a second email address for this student
If EmailAddress <> "" Then

ParentSalutation = Sheets("DOB").Cells(DOBRow, 27).Value '2nd salutation
'Create mail item and send it
Set MailItem = OutlookApp.createitem(olmailitem)
With MailItem
.Subject = Title 'email title
.to = EmailAddress 'parent email
.Body = "Dear " & ParentSalutation & vbCrLf & vbCrLf & TextBody 'body of text
If Attach <> "" Then
.Attachments.Add Attach 'attachment if there is one
End If
.Send
End With

EmailsSent = EmailsSent + 1 'increase count sent by one
'Report the number of emails sent
With Sheets("Pem")
.Range("L29").Value = EmailsSent 'number of emails sent
End With
Set MailItem = Nothing 'kill the email object
End If
End If

With Sheets("Pem")
.Range("L28").Value = DOBRow - 5 'number of students scanned
End With

Next DOBRow

'ALL SENT emails message
Sheets("Pem").Range("j30").Value = "Done on " & Date & " at " & Time
Sheets("Pem").Range("j30").Select
End Sub
 
Thanks for your help.

Would it be necessary to put in the code (after an email is sent) the line :
Set OutlookApp = Nothing
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
I've used a similar process in the past

I found that Outlook couldn't always keep up and so I would display the messages and then sent them manually.

You could try the following to try and slow the process down:

Code:
Application.Wait (Now + TimeValue("0:00:04"))

then do .send
 
Upvote 0
I'd even put the following at the end:

OutlookApp.Quit
Set OutlookApp = Nothing

Again, according to your description, your code stops in the middle of the processing... It should have nothing to do with the clean-up. There's something in the middle of the process...

Try to pinpoint the place when it goes wrong.
Try to use debug.prints throughout your code.
 
Upvote 0
I like the following article:

Considerations for server-side Automation of Office

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757

:)

Quote:
All current versions of Microsoft Office were designed, tested, and configured to run as end-user products on a client workstation. They assume an interactive desktop and user profile, and do not provide the level of reentrancy or security that is necessary to meet the needs of server-side components that are designed to run unattended.

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when run in this environment.
 
Upvote 0

Forum statistics

Threads
1,223,986
Messages
6,175,788
Members
452,670
Latest member
nogarth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top