Attach & Email Excel Workbook from Access Form

[koolwaters]

I am trying to attach and email an Excel workbook from a command button on a form.

The code below opens Outlook, attaches the file but when I enter the email address and click Send, I get a message box "Outlook cannot send this item".

Private Sub cmdEmailApplication_Click()
On Error GoTo Err_cmdEmailApplication_Click

Dim strMessageSubject As String
Dim strBody As String
Dim strTitle As String
Dim Msg As Outlook.MailItem
      
strBody = "Please see attached Application Form"
strMessageSubject = "Application Form"

    Set gappOutlook = GetObject(, "Outlook.Application")
    Set Msg = gappOutlook.CreateItem(olMailItem)
    With Msg
        .Subject = strMessageSubject
        .Body = strBody
        .Attachments.Add GetDBPath & "Application Form Templates\Application Form.xls"
        .Display
        End With

Exit_cmdEmailApplication_Click:
    Exit Sub

Err_cmdEmailApplication_Click:
    MsgBox Err.Description
    Resume Exit_cmdEmailApplication_Click

End Sub

The code below sends the file to the recipient but my challange is that I want to be able to enter the recipient's email address directly from Outlook as it will not always be the same email.

Private Sub cmdEmailApplication_Click()
On Error GoTo Err_cmdEmailApplication_Click

Dim strMessageSubject As String
Dim strBody As String
Dim frm As Access.Form
Dim strTitle As String
Dim Msg As Outlook.MailItem
      
strBody = "Please see attached Application Form"
strMessageSubject = "Application Form"
strToEmail = "mkcc@caribsurf.com"

    Set gappOutlook = GetObject(, "Outlook.Application")
    Set Msg = gappOutlook.CreateItem(olMailItem)
    With Msg
         .To = strToEMail
        .Subject = strMessageSubject
        .Body = strBody
        .Attachments.Add GetDBPath & "Application Form Templates\Application Form.xls"
        .Send
        End With

Exit_cmdEmailApplication_Click:
    Exit Sub

Err_cmdEmailApplication_Click:
    MsgBox Err.Description
    Resume Exit_cmdEmailApplication_Click

End Sub

Thanks for any feedback.

 

[boblarson]

First off - what is the code for

GetDBPath

 

[koolwaters]

Hi Bob,

That is a function that returns the file path of the backend.

 

[boblarson]

Yes, I gathered that. I wanted to see the code to make sure it is returning the path with a backslash at the end. Does it?

 

[koolwaters]

Yes it does.

Here is the code:

Public Function GetDBPath() As String
Dim strFullPath As String
        
    strFullPath = Mid(DBEngine.Workspaces(0).Databases(0).TableDefs("tblStaffList").Connect, 11)
    GetDBPath = Left(strFullPath, InStrRev(strFullPath, "\"))
End Function

 

[boblarson]

Okay, one suggestion - use the TO code for the one that works but display it instead of sending so you can change it. See if that will work. I don't know if you can change it manually that way but it might be possible. I don't have Outlook here at work to test with (I'm stuck with Lotus Notes ).

 

[SydneyGeek]

I've done something similar with Office 2007. In that case, if you display the mail message you are free to put in whatever address you need.
You've probably noticed that you must have an address to display the message; I put in a default NO-ONE@NOWHERE.com and then tell Outlook to clear the To list once the message is displayed, unless there is a real address present.

Denis

 

[koolwaters]

It seems as though the .display is the problem.

Even when I add a recipient, I get the same error "Outlook cannot send this item" once I use .display.

 

[koolwaters]

Hi!

The code works. It seems as though there is an issue with Outlook on the machine I was using.

Private Sub cmdEmailApplication_Click()
On Error GoTo Err_cmdEmailApplication_Click

Dim strMessageSubject As String
Dim strBody As String
Dim frm As Access.Form
Dim strTitle As String
Dim Msg As Outlook.MailItem
      
strBody = "Please see attached Application Form"
strMessageSubject = "Application Form"

    Set gappOutlook = GetObject(, "Outlook.Application")
    Set Msg = gappOutlook.CreateItem(olMailItem)
    With Msg
        .Subject = strMessageSubject
        .Body = strBody
        .Attachments.Add GetDBPath & "Application Form Templates\Application Form.xls"
        .Display
        End With

Exit_cmdEmailApplication_Click:
    Exit Sub

Err_cmdEmailApplication_Click:
    MsgBox Err.Description
    Resume Exit_cmdEmailApplication_Click

End Sub

For those who may be interested, this code is called from a command button on a form. It attaches an excel file in a folder stored in the backend. Because .Display is used, it simply attaches the file, adds a asubject and email body and I can enter the email address/es that I want.

Thanks as usual for the assistance.

 

[SydneyGeek]

Has this PC had an Office upgrade? I've had a similar situation with a client. Works fine on two of my PC's, and a couple of virtual machines that have only ever had one copy of Office installed.
The client regularly upgrades; these PCs have had at least 2003, 2007 and 2010, and the Outlook code hangs on their machines.

FWIW...

Denis