Excel Macro Won't send e-mail when called from Access automatically

[Sullivag2]

Hello every one. I am calling an Excel Macro from Access. I did extensive testing (I thought) last week with the Excel Macro. I did the step through and run to break points and every time the macro would send an e-mail with the attachments and the information it pulled from the one cell of the workbook. If it wasn't for needing that information from the cell for the total lines I could continue to send the e-mail from Access. When I run the access database it will call the macro and format the sheet but it never sends the e-mail unless I already have the Excel macro open and then again walk through or run to a break point and then continue. I get no error messages either when it doesn't work. Any help would be appreciated. I added the loop function to try and give it more time to be able to send the e-mail thinking that maybe it was processing too fast for some reason.


Public Function EmailReport(totalcount)
Dim OutApp As Object
Dim OutMail As Object
Dim strFileName As String
Dim MyDate
Dim Response
Dim DayNumber As Integer
Dim test As Integer
Dim strChartFileName As String
Dim strDetailFileName As String
Dim i As Integer





MyDate = Month(Date) & Day(Date) & Year(Date)







strChartFileName = "C:\Supplier Responsiveness Reports\Supplier Responsiveness Chart " & MyDate & ".xlsx"
strDetailFileName = "C:\Supplier Responsiveness Reports\Supplier Responsiveness Details " & MyDate & ".xlsx"




Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.to = myaddress@server.com (changed to hide)
.CC = ""
.BCC = ""
.Subject = "Supplier Responsiveness Report " & Date
.Body = "Hello Everyone! " & vbNewLine & vbNewLine & "The attached weekly reports look at the PO's that are late to the Planned/Next Need Date." & vbNewLine _
& "The overall count is " & totalcount & " lines" & vbNewLine & "Currently on this file we are showing all suppliers." & vbNewLine _
& "The Detail file is sorted by IM Buyer; Supplier: PO and Position." & vbNewLine & "The Chart file is sorted by Buyer # and then Supllier #. " _
& vbNewLine & vbNewLine _
& "Gary C Sullivan II" & vbNewLine & "Order Fullfillment Analyst" & vbNewLine & "Manitowoc Crane Care" & vbNewLine & & vbNewLine _
& "Integrity Commitment to Stakeholders, and Passion for Excellence"


.attachments.Add strChartFileName
.attachments.Add strDetailFileName


.Send 'or use .Display
End With
On Error GoTo 0


'Trying to give Excel time to process before closing ang going back to Access

i = 1
Do While i < 250
i = i + 1

Loop

Set OutMail = Nothing
Set OutApp = Nothing



End Function