Email File as attachment

H

hcabs99

Active Member
Joined
May 9, 2006
Messages
257
Hi All

I have a database which sends a number of different attachments and different recipients dependant on query results . This all works fine


' SendSites
'
'------------------------------------------------------------
Function SendAutostore()

Set MySet = New ADODB.Recordset
MySet.Open "WMS_EMAILS", CurrentProject.Connection, adOpenStatic

Do Until MySet.EOF
[Forms]![Send].[QSite].Value = MySet![ShippingFrom]
[Forms]![Send].[QEmail].Value = MySet![Addresses (seperate with ;)]




DoCmd.SendObject acQuery, "Qry_Send_Autostore_File", "Microsoft Excel (*.xls)", [Forms]![Send].[QEmail], "", "", "Email Body", False, ""


MySet.MoveNext
Loop

End Function


I have 1 file which i need to send a .csv file via email. Being as Access doesnt allow me to send .csv files automatically, I've had to go down the route of downloading the correctly formatted file on the c drive of the laptop. Thanks to excellent support from xenou, this is now done.

I now need to add the functionality of sending this file to the correct recipient dependant on the query results using the code above

Can i simply change this bit of the code to send the file from the c drive?

DoCmd.SendObject acQuery, "Qry_Send_Autostore_File", "Microsoft Excel (*.xls)", [Forms]![Send].[QEmail], "", "",

Or is it not as simple as that?

Cheers
 
Sort by date Sort by votes
It's up to you. If it's a one off thing I'd put it in the same sub as it's just a few lines of code, though I'd probably resolve this first (below). If it's something you'd do repeatedlyl I may create a separate function for it.

Nothing tested here - just added some lines to Pedie's code.

Code: 
Sub SendEmailwithAttachment()
'Reference to Microsoft Outlook xx.x object library
Dim OutApp As Object
Dim OutMail As Object
Dim rs As DAO.Recordset
    
    CurrentDb.OpenRecordset ("SELECT Addresses FROM WMS_Emails")
    If Not rs.EOF Then
        With rs
            strTo = rs.Fields("Addresses")
        End With
    End If
    rs.Close
    Set rs = Nothing
    
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = "emialid"
        .CC = "emialid"
        .Subject = "Subject line here"
        .Attachments.Add "C:File Fullname.csv"
        .HTMLBody = "Hi "
        .Display
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
xenou said:
It's up to you. If it's a one off thing I'd put it in the same sub as it's just a few lines of code, though I'd probably resolve this first (below). If it's something you'd do repeatedlyl I may create a separate function for it.

Nothing tested here - just added some lines to Pedie's code.

Code: 
Sub SendEmailwithAttachment()
'Reference to Microsoft Outlook xx.x object library
Dim OutApp As Object
Dim OutMail As Object
Dim rs As DAO.Recordset
    
    CurrentDb.OpenRecordset ("SELECT Addresses FROM WMS_Emails")
    If Not rs.EOF Then
        With rs
            strTo = rs.Fields("Addresses")
        End With
    End If
    rs.Close
    Set rs = Nothing
    
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = "emialid"
        .CC = "emialid"
        .Subject = "Subject line here"
        .Attachments.Add "C:File Fullname.csv"
        .HTMLBody = "Hi "
        .Display
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub
Click to expand...

Hi

Plugged this code in as follows

Private Sub Command21_Click()

'Reference to Microsoft Outlook xx.x object library
Dim OutApp As Object
Dim OutMail As Object
Dim rs As DAO.Recordset

CurrentDb.OpenRecordset ("SELECT Addresses FROM WMS_Emails")
If Not rs.EOF Then
With rs
strTo = rs.Fields("Addresses")
End With
End If
rs.Close
Set rs = Nothing


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = strTo
.CC = "emialid"
.Subject = "Subject line here"
.Attachments.Add "C:\Documents and Settings\All Users\Documents\LWS_File.txt"
.HTMLBody = "Hi "
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing

End Sub


However i get the following error:

Run-Time error '91
Object Variable or With block variable not set

Debug highlights this section


If Not rs.EOF Then



Cheers
 
Upvote 0
Change to:
Code: 
[COLOR="Red"]Set rs = [/COLOR]CurrentDb.OpenRecordset ("SELECT Addresses FROM WMS_Emails")
 
Upvote 0
xenou said:
Change to:
Code: 
[COLOR="Red"]Set rs = [/COLOR]CurrentDb.OpenRecordset ("SELECT Addresses FROM WMS_Emails")
Click to expand...

Sorry, nope, same error which highlighted the same bit of code


Private Sub Command21_Click()

'Reference to Microsoft Outlook xx.x object library
Dim OutApp As Object
Dim OutMail As Object
Dim rs As DAO.Recordset

CurrentDb.OpenRecordset ("SELECT Addresses FROM WMS_Emails")
If Not rs.EOF Then
With rs
strTo = rs.Fields("Addresses")
End With
End If
rs.Close
Set rs = CurrentDb.OpenRecordset("SELECT Addresses FROM WMS_Emails")


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = strTo
.CC = "emialid"
.Subject = "Subject line here"
.Attachments.Add "C:\Documents and Settings\All Users\Documents\LWS_File.txt"
.HTMLBody = "Hi "
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing

End Sub
 
Upvote 0
Is this code in an access database? In a module? In a form?
 
Upvote 0
xenou said:
Is this code in an access database? In a module? In a form?
Click to expand...

The code is embedded within a button on a form within the access database,. The existing code (which does not automatically populate the email address) is run in this way

Cheers
 
Upvote 0
Okay, actually now I think you just need to remove the parens. Sorry about that. My bad. I *did* say it was untested.

Code: 
Set rs = CurrentDb.OpenRecordset "SELECT Addresses FROM WMS_Emails"
 
Upvote 0
xenou said:
Okay, actually now I think you just need to remove the parens. Sorry about that. My bad. I *did* say it was untested.

Code: 
Set rs = CurrentDb.OpenRecordset "SELECT Addresses FROM WMS_Emails"
Click to expand...

No need for apolgies, you have helped me out loads here!

This however throws a Compile Error : Expected :End of statement message when i remove the parens on that line

Any suggestions?
 
Upvote 0
Okay, you did need the parens but they were in the wrong place. [note: it was the set keyword that was missing originally, by the way].

Try this (this time it is tested):
Code: 
Set rs = CurrentDb.OpenRecordset("SELECT Addresses FROM WMS_Emails")

Edit:
Actually since this is what I suggested in post 13 it seems there is something else wrong. I'm not sure what. It works fine on my end. Why is it that when you say it doesn't work and show the sample code in post 13 it still has the incorrect syntax for that line? Maybe you still just need to make that change.
 
Last edited:
Upvote 0
xenou said:
Okay, you did need the parens but they were in the wrong place. [note: it was the set keyword that was missing originally, by the way].

Try this (this time it is tested):
Code: 
Set rs = CurrentDb.OpenRecordset("SELECT Addresses FROM WMS_Emails")

Edit:
Actually since this is what I suggested in post 13 it seems there is something else wrong. I'm not sure what. It works fine on my end. Why is it that when you say it doesn't work and show the sample code in post 13 it still has the incorrect syntax for that line? Maybe you still just need to make that change.
Click to expand...

ok, getting a tadge confused, sorry....

Here is my entire code which is throwing the error...

Private Sub Command21_Click()

'Reference to Microsoft Outlook xx.x object library
Dim OutApp As Object
Dim OutMail As Object
Dim rs As DAO.Recordset

CurrentDb.OpenRecordset ("SELECT Addresses FROM WMS_Emails")
If Not rs.EOF Then
With rs
strTo = rs.Fields("Addresses")
End With
End If
rs.Close

Set rs = CurrentDb.OpenRecordset("SELECT Addresses FROM WMS_Emails")

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = strTo
.CC = "emialid"
.Subject = "Subject line here"
.Attachments.Add "C:\Documents and Settings\All Users\Documents\LWS_File.txt"
.HTMLBody = "Hi "
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing

End Sub


Really sorry, but unless i am missing something, i think i have changed this all as per your suggestions...sorry to keep messing you about.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Macro to save file and send emails? Help
Replies
1
Views
1K
emavelo
E
J
  • Question Question
Create Hyperlink to cell referenced file path in VBA automated email.
Replies
0
Views
1K
jenpauh
J
N
  • Question Question
Help to attachment .zip .xlsm file and send email
Replies
0
Views
899
nhnn1986
N
S
  • Question Question
Google sheet: Help to attachment file from Google Drive and send email fill in Google Form
Replies
1
Views
742
sbv1986
S
G
  • Question Question
VBA - Send an email with PDF file attached (exporting active sheet as PDF)
Replies
2
Views
4K
GuillaumeC
G

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top