Emailing with Attachments by clicking a button

**wreck1004** · 11-21-2011, 11:13 AM

Hi all,

I am new to this Forum, and I have only recent (limited) with VBA and object oriented programing. I have made a Form in Microsoft 2003 using a modified verson of this macro. The modified code is below and tied the code to a form button in Access. So when I clicked the button it would send the emails to the emails in the table along with the attachments. I recently upgraded my computer to Microsoft Office 2010 and this macro no longer works.

I currently have a table in Access which contains:
EMAIL | DOCUMENT LOCATION

My company's final goal for this project is to be able to click a button in Access, and on the click send emails to the emails in the table.

What I am looking for is either some code addition to work around in order to allow Microsoft Outlook 2010 to send the emails using the old code or a better way of approaching this macro.

Thank you so much for your help,

Wreck1004

*Below is the second portion of the code in the URL above, the Access portion is the only part I modified*

Code:

Option Compare Database
Private Sub Command0_Click()
' ACCESS VBA MODULE: Send E-mail without Security Warning
' (c) 2005 Wayne Phillips (http://www.everythingaccess.com)
' Written 07/05/2005
' Last updated v1.3 - 11/11/2005
'
' Please read the full tutorial & code here:
' http://www.everythingaccess.com/tutorials.asp?ID=112
'
' Please leave the copyright notices in place - Thank you.
' This is a test function! - replace the e-mail addresses
' with your own before executing!!
' (CC/BCC can be blank strings, attachments string is optional)
    Dim blnSuccessful As Boolean
    Dim strHTML As String
    Dim Lrs As DAO.Recordset
    Dim LSQL As String
    Dim LGST As String
    
    'Open Connection to current Access Database
    Set db = CurrentDb()
    
    LSQL = "SELECT [Carrier Contact Info].[Email Address],[Carrier Contact Info].[File Location] FROM [Carrier Contact Info]"
        
    Set Lrs = db.OpenRecordset(LSQL)
    
    Do While Not Lrs.EOF
                
        strHTML = "<html>" & _
                   "<body>" & _
                   "Carrier" & ",<br>" & _
                   "Attached is your scorecard" & _
                   "<br>" & _
                   "<br>" & "Regards," & "<br>" & _
                   "Sender" & _
                   "<br>" & _
                   "Email" & _
                   "<br>" & "phone #" & _
                   "</body>" & _
                   "</html>"
                   
        blnSuccessful = FnSafeSendEmail(Lrs![Email Address], _
                                        "Carrier Scorecard for " & Lrs![Carrier], _
                                        strHTML, _
                                        Lrs![File Location])
        
    
        If blnSuccessful Then
        
            MsgBox "E-mail message sent successfully!"
            
        Else
        
            MsgBox "Failed to send e-mail!"
        
        End If
        Lrs.MoveNext
    Loop
    
End Sub

'This is the procedure that calls the exposed Outlook VBA function...
Public Function FnSafeSendEmail(strTo As String, _
                    strSubject As String, _
                    strMessageBody As String, _
                    Optional strAttachmentPaths As String, _
                    Optional strCC As String, _
                    Optional strBCC As String) As Boolean
    Dim objOutlook As Object ' Note: Must be late-binding.
    Dim objNameSpace As Object
    Dim objExplorer As Object
    Dim blnSuccessful As Boolean
    Dim blnNewInstance As Boolean
    
    'Is an instance of Outlook already open that we can bind to?
    On Error Resume Next
    Set objOutlook = GetObject(, "Outlook.Application")
    On Error GoTo 0
    
    If objOutlook Is Nothing Then
    
        'Outlook isn't already running - create a new instance...
        Set objOutlook = CreateObject("Outlook.Application")
        blnNewInstance = True
        'We need to instantiate the Visual Basic environment... (messy)
        Set objNameSpace = objOutlook.GetNamespace("MAPI")
        Set objExplorer = objOutlook.Explorers.Add(objNameSpace.Folders(1), 0)
        objExplorer.CommandBars.FindControl(, 1695).Execute
                
        objExplorer.Close
                
        Set objNameSpace = Nothing
        Set objExplorer = Nothing
        
    End If
    blnSuccessful = objOutlook.FnSendMailSafe(strTo, strCC, strBCC, _
                                                strSubject, strMessageBody, _
                                                strAttachmentPaths)
                                
    If blnNewInstance = True Then objOutlook.Quit
    Set objOutlook = Nothing
    
    FnSafeSendEmail = blnSuccessful
    
End Function

**June7** · 11-22-2011, 03:02 AM

This isn't a macro, it is VBA and should work in 2010. Assume code will compile? Have you step debugged? Upgrade might require changing a VBA library reference.

**wreck1004** · 11-22-2011, 09:32 AM

June 7, which part would you recommend I step Debug? the Access portion of the code or the Outlook portion. On the website I got this code from, the comments by the user who made the code said he had not figured out a way to make his code work in Office 2010. I went through and Debugged the Outlook portion of the code, and that part seems to be working.

I tried debugging the Access portion above, and i kept geting Error '424' Runtime error. I think it is because I couldn't figure out how to debug this portion.

I appologize for the stupid question, but by "Assume code will compile", what exactly do you mean by that?

**June7** · 11-22-2011, 02:11 PM

On VBA editor menu > Debug > Compile. Any error messages?

**wreck1004** · 12-05-2011, 01:16 PM

June7: I ran the Compile, and there were no error messages. The Compile Button turned into a form where I could no longer click on it either. Any other advise?

**June7** · 12-06-2011, 08:57 PM

Once Compile runs successfully, the button is not available because there is no reason for it to be available.

If you have step debugged, which line does execution fail on? Review the link in my signature at bottom of post for debug techniques. I cannot test code until after the 15th (vacation).

**wreck1004** · 12-14-2011, 09:49 AM

I tried debugging both portions of the program, and there appears to be nothing wrong with either portion. The portion of the code that goes into Outlook, when tested will send an email to the test email. As I said before I tried compiling the Access portion of the code and the Compile button went grey. I am not sure what else to do. Thank you for your help June7

**June7** · 12-17-2011, 08:06 PM

What line triggers the Error '424' Runtime error?

I have confirmed that the code in the Outlook VBA does work. I get error in the Access module on the line that calls the Outlook function. Keeps telling me object does not support method. I have the Outlook library reference set. I am at a loss as to why this fails.

I wonder if something changed with 2010. I have a very simple email procedure in 2007 that is coded only in Access VBA and I don't get that nuisance warning popup but the same code in 2010 does.

Emailing with Attachments by clicking a button

Thread Tools

Emailing with Attachments by clicking a button

Similar Threads

Emailing Report with Attachments

Comments when clicking a button

Clicking links in webpages

Enable button in the master form when clicking in the detail

Clicking on label adds new record?

Posting Permissions