Hi everyone,
I have some code that runs in MS Access which opens outlook, attaches a file then sends using the Sendkey "%s" command. I was given the code by a friend as my programming skills are limited, but it generally works without a problem. However, not all the time! every now and again it simply hangs. Access will shut down without sending the email, just leaving it hanging there untill I arrive at work and manually have to hit the send key. Its quite frustrating as I have the macro set to run on weekends and when it falls then, I have to come in and send it.
Here is the code below. Anyone got any ideas on a way to ensure that it is sent? or an easier way to code it? Many thanks in advance. GS
Sub mcr_Email_File_JDN_DSR()
Dim objol As New Outlook.Application
Dim objmail As MailItem
Set objol = New Outlook.Application
Set objmail = objol.CreateItem(olMailItem)
On Error Resume Next
WaitFor (15)
With objmail
.To = "gerry sullivan" ' enter in here the email addresses
'.CC = "Ben smith " 'enter in here the CC email address
.Subject = "Daily Sales and KPI Report "
.NoAging = True ' [True | False]
.BodyFormat = olFormatPlain ' [olFormatPlain | olFormatRichText | olFormatHTML | olFormatUnspecified]
.ReadReceiptRequested = False ' [True | False]
.Attachments.Add "O:\Finance_Store_Merchandise_Reports\Daily_sales_report\Daily_Sales_JDN_V2.xls"
.Body = "This is an automated message so if you find any errors, or if you " & _
"have any questions regarding this information, please contact myself directly. " & _
"All amounts are in local currency." & _
vbCrLf & vbCrLf & _
"Regards" & vbCrLf & "Gerry" & vbCrLf & "040 2400618"
.Display
End With
Set objmail = Nothing
Set objol = Nothing
'Wait for System to catch up and send using previous Public Function
WaitFor (15)
SendKeys "%s", True
On Error GoTo 0
End Sub
I have some code that runs in MS Access which opens outlook, attaches a file then sends using the Sendkey "%s" command. I was given the code by a friend as my programming skills are limited, but it generally works without a problem. However, not all the time! every now and again it simply hangs. Access will shut down without sending the email, just leaving it hanging there untill I arrive at work and manually have to hit the send key. Its quite frustrating as I have the macro set to run on weekends and when it falls then, I have to come in and send it.
Here is the code below. Anyone got any ideas on a way to ensure that it is sent? or an easier way to code it? Many thanks in advance. GS
Sub mcr_Email_File_JDN_DSR()
Dim objol As New Outlook.Application
Dim objmail As MailItem
Set objol = New Outlook.Application
Set objmail = objol.CreateItem(olMailItem)
On Error Resume Next
WaitFor (15)
With objmail
.To = "gerry sullivan" ' enter in here the email addresses
'.CC = "Ben smith " 'enter in here the CC email address
.Subject = "Daily Sales and KPI Report "
.NoAging = True ' [True | False]
.BodyFormat = olFormatPlain ' [olFormatPlain | olFormatRichText | olFormatHTML | olFormatUnspecified]
.ReadReceiptRequested = False ' [True | False]
.Attachments.Add "O:\Finance_Store_Merchandise_Reports\Daily_sales_report\Daily_Sales_JDN_V2.xls"
.Body = "This is an automated message so if you find any errors, or if you " & _
"have any questions regarding this information, please contact myself directly. " & _
"All amounts are in local currency." & _
vbCrLf & vbCrLf & _
"Regards" & vbCrLf & "Gerry" & vbCrLf & "040 2400618"
.Display
End With
Set objmail = Nothing
Set objol = Nothing
'Wait for System to catch up and send using previous Public Function
WaitFor (15)
SendKeys "%s", True
On Error GoTo 0
End Sub