Email with Attachments
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
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?
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Email with Attachments
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.
And yes, the idea was to replace "email address" with "leesha@somewhere.com" or whichever email address you want to use.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
I did try Alt F11 but I get the lenovo popup.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
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")
This is what I have inserted a after .body
.SendUsingAccount = outApp.GetNameSpace("MAPI").Accounts("sales@shutterbugdesigns.com")
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Email with Attachments
If that doesn't work, I'm afraid I can't help you. Sorry!
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
I appreciate the help!
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
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
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
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
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.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
Thanks Hans! Sometimes when I "think like Hans" I figure it out.
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
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
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
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Email with Attachments
SendUsingAccount is a property of outMsg but you haven't set outMsg at that point.
Move the offending line to below
If that doesn't work, also change the line to
Move the offending line to below
Code: Select all
With outMsg
Code: Select all
Set .SendUsingAccount = outApp.GetNameSpace("MAPI").Accounts("sales@shutterbugdesigns.com")
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
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
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
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Email with Attachments
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
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
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
Thanks so much,
Leesha
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
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
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
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
Here you go.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Email with Attachments
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...
So gstrPath remains empty...
Best wishes,
Hans
Hans