Solved Saving an Outlook Message Error (1 Viewer)

pooldead

Registered User.
Local time
Today, 15:11
Joined
Sep 4, 2019
Messages
118
I have the following functions that are used to generate an Outlook email. It works fine, unless I include an "attach1". Then the email that gets created does not include the attachment I specified as variable "attach1", but instead includes the saved version of the same email.

Code:
Public Function composeLate(appName As String, ticketNum As String, dueDate As String, attach2 As String, eTo As String, Optional ePath As String, Optional eCC As String)

    Dim subj As String, body As String, sig As String, attach1 As String, eImp As String
    
    subj = "<REMOVED>"
    body = "<REMOVED>"
    sig = "<REMOVED>"
    attach1 = "\\REMOVED\Test.pptx"
    eImp = "olImportanceHigh"
    
    CheckReturn = sendEmail(subj, body, sig, eTo, ePath, , attach2, eCC, eImp)

End Function

Public Function sendEmail(eSubject As String, eBody As String, sig As String, eTo As String, Optional ePath As String, Optional attach1 As String, Optional attach2 As String, Optional eCC As String, Optional eImp As String) As Boolean

    Dim outApp As Object, outMail As Outlook.MailItem
    
    Set outApp = CreateObject("Outlook.application")
    Set outMail = outApp.CreateItem(0)
    
    With outMail
        .To = eTo
        .CC = "<REMOVED>;" & eCC
        .SentOnBehalfOfName = "<REMOVED>"
        .Subject = eSubject
        If Not attach1 = "" Then
            .Attachments.Add (attach1)
        End If
        If Not attach2 = "" Then
            .Attachments.Add (attach2)
        End If
        If Not eImp = "" Then
            .Importance = olImportanceHigh
        End If
        .Display
        .HTMLBody = eBody & sig
        .SaveAs ePath, olMSG
        .Display
    End With
    
    Set outMail = Nothing
    Set outApp = Nothing

End Function

How can I adjust my code so that I can use .SaveAs and include the "attach1" file?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:11
Joined
Oct 29, 2018
Messages
16,611
Hi. Just a guess, but try removing the enclosing parens in your attachments. For example:
Code:
.Attachments.Add attach1
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:11
Joined
Sep 21, 2011
Messages
9,083
I have no idea as to why it is saving itself, but you are not passing attach1, but attach2, which you have not given any value to?
 

pooldead

Registered User.
Local time
Today, 15:11
Joined
Sep 4, 2019
Messages
118
I have no idea as to why it is saving itself, but you are not passing attach1, but attach2, which you have not given any value to?
Sorry, I forgot I took attach1 out of the function call when I was testing. Yes, attach1 would be inserted in between ePath and attach2
 

pooldead

Registered User.
Local time
Today, 15:11
Joined
Sep 4, 2019
Messages
118
Hi. Just a guess, but try removing the enclosing parens in your attachments. For example:
Code:
.Attachments.Add attach1
Just tried, but no luck. It still attached the saved message instead of the PPTX
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:11
Joined
Oct 29, 2018
Messages
16,611
Just tried, but no luck. It still attached the saved message instead of the PPTX
Sorry to hear that. Have you tried stepping through the code? Just curious...
 

pooldead

Registered User.
Local time
Today, 15:11
Joined
Sep 4, 2019
Messages
118
Sorry to hear that. Have you tried stepping through the code? Just curious...
I have tried, but I'm not getting anything back. I'm not the best at stepping into yet, so I'm not sure if it's not finding anything because it's one function sending to another
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:11
Joined
Oct 29, 2018
Messages
16,611
I have tried, but I'm not getting anything back. I'm not the best at stepping into yet, so I'm not sure if it's not finding anything because it's one function sending to another
As you step through the code, you can examine the content of each variable to make sure you are getting what you were expecting.
 

pooldead

Registered User.
Local time
Today, 15:11
Joined
Sep 4, 2019
Messages
118
As you step through the code, you can examine the content of each variable to make sure you are getting what you were expecting.
Unfortunately, it isn't giving any results.
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:11
Joined
Sep 21, 2011
Messages
9,083
I've just taken your code, and called it from the immediate window, and it works for me, except the Save option?
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:11
Joined
Sep 21, 2011
Messages
9,083
I now have the save option working if I give a valid filename for epath?
 

pooldead

Registered User.
Local time
Today, 15:11
Joined
Sep 4, 2019
Messages
118
I now have the save option working if I give a valid filename for epath?
Other providing a variable for ePath (which in my code comes from somewhere else), did you make any code changes to what I posted? I tried from Immediate as you did. I was receiving an error "Ambiguous Name Detected ePath", but in researching that error, I can't tell what it means.
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:11
Joined
Sep 21, 2011
Messages
9,083
Other providing a variable for ePath (which in my code comes from somewhere else), did you make any code changes to what I posted? I tried from Immediate as you did. I was receiving an error "Ambiguous Name Detected ePath", but in researching that error, I can't tell what it means.
I am just trying from the Immediate window as I do not have your set up.? Call your email function from there. Ambiguous means you have the name more than once?

Put a breakpoint on the With OutMail line and step through with F8 and inspect your variables.

FWIW the attachments.add works with () or without.
 

pooldead

Registered User.
Local time
Today, 15:11
Joined
Sep 4, 2019
Messages
118
Is the way I did multiple attachments correct? I think I've actually narrowed the problem down to including 2 attachments.
 

pooldead

Registered User.
Local time
Today, 15:11
Joined
Sep 4, 2019
Messages
118
1598991071906.png

Here is what I am getting in the email. I've discovered the .SaveAs doesn't matter. For some reason, when I include a second attachment (in this case, attach1), that Outlook Item gets attached (the exact same one every time). It should be a PowerPoint file.

It seems like either Access or Outlook has a particular string stored for that first attachment that is preventing the PowerPoint being attached. I'm leaning towards Outlook being the culprit, but I'm not sure how to clear the cache or any of the variables.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 23:11
Joined
Sep 21, 2011
Messages
9,083
TBH the culprit is you. :)
Pleas show us the EXACT code you are using, using copy and paste.

It is OK to amend the personal data, but the leave the rest of the code alone, leave it as it is.

Also, for testing, put the attachments where we can see them, like "C:\Temp\"
 
Last edited:

pooldead

Registered User.
Local time
Today, 15:11
Joined
Sep 4, 2019
Messages
118
TBH the culprit is you. :)
Pleas show us the EXACT code you are using, using copy and paste.

It is OK to amend the personal data, but the leave the rest of the code alone, leave it as it is.
No doubt I did something wrong (wouldn't be the first time!) :) I have played with the code a bit since I originally posted, so some of it may look a little different.
Code:
Step 1:
Private Sub cmdLate_Click()

    Select Case Forms!Homepage.optGroup
        Case 5
            Select Case Forms!Homepage.optGroupMgr
                Case 1
                    App.App_Late ("App_M2") <<<This is where the code is running for now
                Case 2
                    App.App_Late ("App_O2")
                Case Else
                    MsgBox "Please select either the Manager or Owner phase on the Homepage.", vbExclamation + vbOKOnly, "OOPS!"
            End Select
        Case Else
            MsgBox "Please select an application from the list on the Homepage.", vbExclamation + vbOKOnly, "OOPS!"
    End Select

End Sub

Step 2:
Public Function App_Late(tableName)

    Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset
    
    ticketNum = "" & Forms!Homepage.txtTicket & ""
    dueDate = "" & Forms!Homepage.txtDue & ""
    dtSave = Format(Now(), "MM-dd-yy")

    sqlStr = "SELECT [Mgr Start] FROM sysInfo WHERE Application = 'App';"
    Set rs1 = CurrentDb.OpenRecordset(sqlStr)
        strYear = Format(rs1.Fields("Mgr Start"), "YYYY")
        strMonth = Format(rs1.Fields("Mgr Start"), "MM")
        
        Select Case strMonth
            Case Is = "01"
                strMonth = "1. January"
            Case Is = "04"
                strMonth = "4. April"
            Case Is = "07"
                strMonth = "7. July"
            Case Is = "10"
                strMonth = "10. October"
        End Select
            filePath = "PATH\App\Original Worksheets\"
            strPath = "PATH\App\Emails\Sent Emails\"
        rs1.Close
    Set rs1 = Nothing

    sqlStr = "SELECT DISTINCT [Manager ID] " & _
                "FROM " & tableName & " " & _
                "WHERE Response Is Null;"
    Set rs1 = CurrentDb.OpenRecordset(sqlStr)
        Select Case Forms!Homepage.optGroupMgr
            Case 1
                If rs1.RecordCount = 0 Then
                    MsgBox "There were no managers found to have not responded." & vbNewLine & vbNewLine & _
                            "If this is an error, please contact x.", vbExclamation + vbOKOnly, "Reminder"
                    Exit Function
                Else
                    Do While Not rs1.EOF
                        fileName = filePath & rs1.Fields("Manager ID") & ".xlsm"
                            attach2 = fileName
                        ePath = strPath & rs1.Fields("Manager ID") & "_LATE_" & dtSave & ".MSG"
                        
                        eTo = rs1.Fields("Manager ID")
                        
                        If rs1.Fields("Manager ID") = "abc" Then
                            eCC = "def"
                            CheckReturn = composeLate("App", ticketNum, dueDate, attach2, eTo, eCC, ePath)
                        Else
                            CheckReturn = composeLate("App", ticketNum, dueDate, attach2, eTo,,ePath)
                        End If
                        
                        rs1.MoveNext
                    Loop
                End If
            End Select
        rs1.Close
    Set rs1 = Nothing

End Function

Step 3:
Public Function composeLate(appName As String, ticketNum As String, dueDate As String, attach2 As String, eTo As String, Optional eCC As String, Optional ePath as String)

    Dim subj As String, body As String, sig As String, attach1 As String, eImp As String, attach3 As String
    
    subj = "LATE - ACTION REQUIRED | " & appName & " | QTR Access Certification | " & ticketNum & " - " & dueDate
    
    body = "EMAIL BODY"
    
    sig = "EMAIL SIGNATURE"
    
    attach3 = "PATH\HPAccessReviewMGR.pptx"
    
    eImp = "olImportanceHigh"
    
    CheckReturn = generateEmail(subj, body, sig, eTo, attach3, attach2, eCC, eImp)

End Function

Step 4:
Public Function generateEmail(eSubject, eBody, sig, eTo, Optional attach1, Optional attach2, Optional eCC, Optional eImp, Optional ePath As String) As Boolean

    Dim outApp As Object, outMail As Outlook.MailItem
    
    Set outApp = CreateObject("Outlook.Application")
    Set outMail = outApp.CreateItem(0)
    
    With outMail
        .Display
        .To = eTo
        .CC = "EMAIL ADDRESS;" & eCC
        .SentOnBehalfOfName = "EMAIL ADDRESS"
        .Attachments.Add attach1
        .Attachments.Add attach2
        If eImp <> "" Then
            .Importance = olImportanceHigh
        End If
        .Subject = eSubject
        .HTMLBody = eBody & sig
        If ePath <> "" Then
            .SaveAs ePath
        End If
    End With
    
    Set outMail = Nothing
    Set outApp = Nothing

End Function
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:11
Joined
Sep 21, 2011
Messages
9,083
Well it looks OK to me.
Break it down into sections to test.

In notepad write the call to generateEmail with hardcoded parameters. Then paste into the immediate window and run that.
I'd still set a breakpoint where I mentioned before and step through with F8.

Once you get that working repeat the process for the function that calls that function and repeat up the chain, until you find the problem.

That is how I would approach the problem as nothing jumps out to me. :(

However I can see you are not passing ePath though to the last function.? plus your paramters to to composelate are not consistent. ?
 

pooldead

Registered User.
Local time
Today, 15:11
Joined
Sep 4, 2019
Messages
118
Well it looks OK to me.
Break it down into sections to test.

In notepad write the call to generateEmail with hardcoded parameters. Then paste into the immediate window and run that.
I'd still set a breakpoint where I mentioned before and step through with F8.

Once you get that working repeat the process for the function that calls that function and repeat up the chain, until you find the problem.

That is how I would approach the problem as nothing jumps out to me. :(
Thanks @Gasman, I'll give that a shot! I'm glad to know at least there isn't anything obvious with my code. I am playing around with the following as a second option, but I'm struggling to figure out how I would pass it my attachment paths...? (Source: Multiple Attachments)
Code:
' Add attachments to the message.
        If Not IsMissing(vAttachments) Then
            If IsArray(vAttachments) Then
                For i = LBound(vAttachments) To UBound(vAttachments)
                    If vAttachments(i) <> "" And vAttachments(i) <> "False" Then
                        Set oOutlookAttach = .Attachments.Add(vAttachments(i))
                    End If
                Next i
            Else
                If vAttachments <> "" Then
                    Set oOutlookAttach = .Attachments.Add(vAttachments)
                End If
            End If
        End If
 

Users who are viewing this thread

Top Bottom