Email with Attachments

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Email with Attachments

Post by Leesha »

Hi,
The code below is the code I've been using to send emails from Access. It works fine. What I need to know is if it's possible to be able to add 1 or more attachments to it that are saved to a folder on my computer. Outlook does not have all of the user emails, as they are stored in Access and are always being added to/edited. Since I can't link Outlook to Access to keep it update with the client data that is Access it would be easier to use Access for the emails, esp. if they are bulk emails.
Thanks,
Leesha

Code: Select all

    Dim strWhere As String
    Dim strSQL As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strFilename As String
    Dim outApp As Object
    Dim outMsg As Object
    Dim blnStart As Boolean
    Dim arrNames As Variant
    Dim i As Long

    'checks to be sure reason for email is filled out
    If IsNull(Me.Comment10) Then
        MsgBox "There must be a description of the reason for the email before the emails can be sent."
        Me.Comment10.SetFocus
        Exit Sub
    End If

    If IsNull(Me.Subject) Then
        MsgBox "The subject for the email must be entered before the emails can be sent."
        Me.Subject.SetFocus
        Exit Sub
    End If

    On Error Resume Next
    ' Try to get running instance of Outlook
    Set outApp = GetObject(Class:="Outlook.Application")
    If outApp Is Nothing Then
        ' If Outlook wasn't running, start it
        Set outApp = CreateObject(Class:="Outlook.Application")
        If outApp Is Nothing Then
            ' We failed to start Outlook, so get out
            MsgBox "We can't start Outlook, sorry!", vbCritical
            Exit Sub
        End If
        ' Set a flag that we started Outlook
        blnStart = True
    End If
    On Error GoTo ErrHandler

    ' Create the where-condition
    strWhere = " WHERE EmailFaxSent = False"
    strSQL = "SELECT ALL * FROM [qryEmailSpreadsheetBulkInsurance]" & strWhere

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

    Do While Not rst.EOF
    
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     glngInvoiceID = rst!InvoiceID
        strFilename = gstrPath & rst![WALMART NO] & "-" & _
            rst!STORE_ID & "Insurance Info Request.pdf"
 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

        
        
        ' Create a new e-mail message
        Set outMsg = outApp.CreateItem(0) ' olMailItem
        With outMsg
            ' Use the e-mail address field
            'Main Email
            arrNames = Split(rst![FranchiseeEmail], ",")
            For i = 0 To UBound(arrNames)
                .Recipients.Add arrNames(i)
            Next i

            If Not IsNull(rst![Contact5Email]) Then
               arrNames = Split(rst![Contact5Email], ",")
                For i = 0 To UBound(arrNames)
                  ' .Recipients.Add arrNames(i)
                .Recipients.Add(arrNames(i)).Type = 2
                Next i
            End If

            ' Change the subject as needed
            .Subject = rst![WALMART NO] & "-" & _
            rst!STORE_ID & "  Insurance Info Request"
            
            .Body = Me.Comment1
            .Send ' to send the message immediately
        End With

        'Set EmailFaxSent field to True, and EmailDateTimeSent to Now
        rst.Edit
        rst!EmailFaxSent = True
        rst!EmailDateSent = Date
        rst.Update
        rst.MoveNext
    Loop

    MsgBox "Emails have been sent"

    DoCmd.Close acForm, "frmOutputEmail"

ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    If blnStart Then
        outApp.Quit
    End If
    Set outMsg = Nothing
    Set outApp = Nothing
    Exit Sub

ErrHandler:
    If Err = 2501 Then
        Resume Next
    Else
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End If

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Email with Attachments

Post by HansV »

Anywhere between the line

Code: Select all

        With outMsg
and the lines

Code: Select all

            .Send ' to send the message immediately
        End With
Insert one or more lines like this (one for each attachment):

Code: Select all

            .Attachments.Add "C:\MyFolder\MySubFolder\SomeFile.docx"
You'll have to change the path and filename of course.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Email with Attachments

Post by Leesha »

Hi Hans,
2 questions, the file name won't always be the same. First, how do I account for that without having the change the code each time? Second, will this allow me to attach multiple files or is that even possible? Also these will be PDF's as well as word Docs.
Thanks,
Alicia

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Email with Attachments

Post by HansV »

1) Are the file names stored in a field or fields in the query? If not, how do you want to specify the files? Do you want the code to prompt the user to select them?
2) Yes, as I wrote: "Insert one or more lines like this (one for each attachment)". so if you want to add 3 attachments, create 3 lines with .Attachments.Add.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Email with Attachments

Post by Leesha »

Hi Hans,
I was so excited that this could be done that I missed the note re adding the code more than once. :sad: Needless to say I'm pretty tickled the it can be done!

Since the attachments will be standard, the names are stored in a table. I am intrigued with the possibility of the user being able to select an attachment with a different name that may not be in the table.

As always, I'm adapting code from the original code you gave me years ago. I'm attaching a stripped down version of the database with the email form. I've tried adjusting everything to the current control names, but of course I'm getting errors that I don't know how to fix. The first one I'm getting is "variable not defined" and it refers to glngCleintID = rst!ClientID. I'm not even sure if I need this code but when I took it out I got different errors.

I added the code for the attachments but am not sure what to put in for the name of the document. Do I put in the control that it refers to?

Thanks so much!! I'm very excited.
Leesha
You do not have the required permissions to view the files attached to this post.

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Email with Attachments

Post by Leesha »

Hi Hans,
Just wondering if want I'm aiming to do is possible or if I should try an alternative approach?
Thanks,
Leesha

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Email with Attachments

Post by HansV »

There must have been a declaration of glngClientID in the original code - probably in a standard module. Same for gstrPath - was that a constant?
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Email with Attachments

Post by Leesha »

Hi Hans,
I got the original code from you years ago and just followed your instructions on how to implement. Sure enough I checked to see if there was a module and there was! I don't need it for this as far as I know so I took out the code. Now I get an error that says "Too few parameters. Expected 1". As I'm sure you can imagine I have no idea what to do with this. Where do I look next?
Thanks so much,
Leesha

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Email with Attachments

Post by HansV »

I have to leave now, I'll look later.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Email with Attachments

Post by HansV »

See the attached version.
I added a module with the declarations. In order to test, I used a path on my own PC for gstrPath.
You'll have to change it to the path you want to use before testing.

Hans.zip
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Email with Attachments

Post by Leesha »

Hi Hans,
Never mind, I think I just found my answer to the question below!

Is it supposed to open up an email with the attachments in my Outlook and then I send from there or should it just go out from Access. My goal is that is go out from Access without the Outlook pop up as this will be used for bulk emails as well as individual emails.
I'm so impressed with how the code works at picking up the attachments from being chosen in the drop down boxes!
Thanks,
Alicia

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Email with Attachments

Post by Leesha »

This works perfectly!!! Thanks so much!! I could never have done this on my own.
Leesha

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Email with Attachments

Post by HansV »

Great!
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Email with Attachments

Post by Leesha »

Quick question (I hope) .... when regular emails are sent by the user (not via Access) they have a logo and signature on them. The ones I'm testing with do not go out with the formatted signature and logo. Is there anyway that can be done or specified in the code? If not they will live with it.
Thanks!

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Email with Attachments

Post by HansV »

It would be much more complicated.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Email with Attachments

Post by Leesha »

Thanks anyway Hans. Hopefully they will be thrilled with what I'm giving them. I certainly am.

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Email with Attachments

Post by Leesha »

Hi Hans,
Back again. This code is working great. The only issue the user runs into is that when the DB is on a different computer, the email doesn't run correctly due to the code in module1. Is it possible to update this code from a form? The thought is that the user would enter their computer username into a text box and click a button that would update the code below with the change in the user name.
Thanks!
Leesha

Public glngClientID As Long
Public Const gstrPath As String = "C:\Users\Leesh\OneDrive\Documents\YLCustomerDatabase\YLCustomerDatabaseFiles\EmailAttachments\"

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Email with Attachments

Post by HansV »

Change the line

Code: Select all

Public Const gstrPath As String = "C:\Users\Leesh\OneDrive\Documents\YLCustomerDatabase\YLCustomerDatabaseFiles\EmailAttachments\"
to

Code: Select all

Public gstrPath As String
I assume that your database has a startup form. If it already has a Form_Load event procedure, add the following line to it. Otherwise, create the Form_Load event procedure and add the line.

Code: Select all

    gstrPath = Environ("OneDrive") & "\Documents\YLCustomerDatabase\YLCustomerDatabaseFiles\EmailAttachments\"
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Email with Attachments

Post by Leesha »

Quick question, is there a way for the Environ to determine if the user is using OneDrive or simply installing to documents or do I need to set up two different databases to deploy? One that would be for a C drive install and one for OneDrive?
Thanks!

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Email with Attachments

Post by HansV »

Will the database itself always be in ...\YLCustomerDatabaseFiles ? Or can users decide to install it elsewhere?
Best wishes,
Hans