Email with Attachments

[Leesha]

I don't see the option the VB editor in Outlook. Also, I'm assuming that were the code says "Email Address" that is where I put the email address?

[HansV]

Have you tried pressing Alt+F11 in Outlook?

And yes, the idea was to replace "email address" with "leesha@somewhere.com" or whichever email address you want to use.

[Leesha]

I did try Alt F11 but I get the lenovo popup.

[HansV]

How about Alt+Fn+F11?

[Leesha]

You amaze me! I tried every combo but that one. Ran the code and the email I'm using showed up. I copied and pasted it to be sure it's correct and I get the same error.

This is what I have inserted a after .body
.SendUsingAccount = outApp.GetNameSpace("MAPI").Accounts("sales@shutterbugdesigns.com")

[HansV]

If that doesn't work, I'm afraid I can't help you. Sorry!

[Leesha]

I appreciate the help!

[Leesha]

Hi,
I am getting a write conflict error and am not sure what to do to stop it. The user has the option to send bulk email. If the email blows up they can open frmEmailAddressLookup which opens to datasheet view. It is bound to qryEmailSentLookup. The can fix the email that stopped the code or delete the record, close the form and return to the form with command to send the email and start again. The form the sends the email is called frmOutputEmailBatch and the query it is bound to is qryEmailSent. Both queries a built from tblEamilSubjectCommentTemp.

The batch emails go out without an issue unless the user makes a change to and email address on frmEmailAddreassLookup. If they make a change and go back send the emails the write conflict error comes up. I tried setting the frmEmailAddressLookup to save on close but that didn't work and I tried setting the warnings to false then true to try to block the popup but that didn't work. The code marks the record as sent and I believe that is where the error is coming from if the user made a change.

Long story short, what do I do to prevent this error?

Thanks,
Leesha

[Leesha]

I "think" I may have found the issue with this. There was code to save the record and when I took it out I am no longer getting the error and all seems fine in the tables. Keeping my fingers crosed.

[HansV]

Good to hear that

[Leesha]

Thanks Hans! Sometimes when I "think like Hans" I figure it out.

[Leesha]

Hi Hans,
I'm back at trying to send this from a specified email address, not the default email address. The code below works fine until I put in the .sendusingaccount piece. I've attached the error I'm getting. What am I doing wrong?
Thanks,
Leesha

Code: Select all

Private Sub cmdEmailPrint_Click()
    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


    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 [qryEmailSent]" & strWhere

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

    Do While Not rst.EOF
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        glngClientID = rst!ClientID
        'strFilename = gstrPath & rst![ClientID]
 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        
.SendUsingAccount = outApp.GetNameSpace("MAPI").Accounts("sales@shutterbugdesigns.com")
        
        ' 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![EmailAddress], ",")
            For i = 0 To UBound(arrNames)
                .Recipients.Add arrNames(i)
            Next i

            ' Change the subject as needed
            .Subject = Me.Subject
            

            .Body = Me.Comment
            '.Display ' to view the message and send it manually
            .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 - THERE IS NO EmailDateSent field in the query!
        rst.Update
        rst.MoveNext
    Loop
    
    MsgBox "Emails have been sent"

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
End Sub

[HansV]

SendUsingAccount is a property of outMsg but you haven't set outMsg at that point.
Move the offending line to below

Code: Select all

        With outMsg

If that doesn't work, also change the line to

Code: Select all

            Set .SendUsingAccount = outApp.GetNameSpace("MAPI").Accounts("sales@shutterbugdesigns.com")

[Leesha]

Hi Hans,
I tried it both ways but each time get an error "Wrong number of arguments or invalid property assignment." I've reattached the db.
Thanks,
Leesha

[HansV]

Try this version:

Code: Select all

Private Sub cmdEmailPrint_Click()
    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
    Dim outNsp As Object
    Dim outAct As Object

    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
    Set outNsp = outApp.GetNameSpace("MAPI")
    For Each outAct In outNsp.Accounts
        If outAct = "sales@shutterbugdesigns.com" Then Exit For
    Next outAct
    If outAct Is Nothing Then
        MsgBox "Account not found!", vbCritical
        GoTo ExitHandler
    End If

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

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

    Do While Not rst.EOF
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        glngClientID = rst!ClientID
        'strFilename = gstrPath & rst![ClientID]
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' Create a new e-mail message
        Set outMsg = outApp.CreateItem(0) ' olMailItem
        With outMsg
            Set .SendUsingAccount = outAct
            ' Use the e-mail address field
            'Main Email
            arrNames = Split(rst![EmailAddress], ",")
            For i = 0 To UBound(arrNames)
                .Recipients.Add arrNames(i)
            Next i

            ' Change the subject as needed
            .Subject = Me.Subject

            .Body = Me.Comment
            '.Display ' to view the message and send it manually
            .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 - THERE IS NO EmailDateSent field in the query!
        rst.Update
        rst.MoveNext
    Loop
    
    MsgBox "Emails have been sent"

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
End Sub

[Leesha]

Hans you are the most wonderful man! I love your brain and that you know how to do this stuff. It worked like a charm!
Thanks so much,
Leesha

[Leesha]

Hi Hans,
I'm giving in to defeat. I've been at this for hours and for the life of me can't figure out / remember how the code knows where to get the attachment.

I took out the attachments from the database when trying to troubleshoot having the email go out with a specific email address (which you fixed above). I added back the code for the attachments and the emails still work fine however they don't add the attachments. I put the path into tblAdministrativeInformation but its still not adding the attachments.

This is a single database and I am the only user for the time being so I can put the path directly into the code vs a table if that makes it easier, but really don't know how to do that. I've looked at the modules you gave me but am not sure what I'm reading in ModuleEmail2. When you have a minute I'd appreciate your help.


Thanks,
Leesha

[HansV]

Can you attach the version that doesn't work?

[Leesha]

Here you go.

[HansV]

The value of the variable gstrPath used to be set in the On Load event procedure of frmLogon, but that form is not included in this database.
So gstrPath remains empty...