Email File as attachment

[hcabs99]

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

 

[pedie]

Since you already have the query exported to C drive you can attach file this way...

Example code below:

[/FONT]
[FONT=Courier New]'Reference to Microsoft Outlook xx.x object library
[/FONT]
[FONT=Courier New]Sub SendEmailwithAttachment()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)[/FONT]
[FONT=Courier New]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[/FONT]
[FONT=Courier New]Set OutMail = Nothing
Set OutApp = Nothing
End Sub[/FONT]
[FONT=Courier New]

 

[hcabs99]

Since you already have the query exported to C drive you can attach file this way...

Example code below:

[/FONT]
[FONT=Courier New]'Reference to Microsoft Outlook xx.x object library
[/FONT]
[FONT=Courier New]Sub SendEmailwithAttachment()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)[/FONT]
[FONT=Courier New]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[/FONT]
[FONT=Courier New]Set OutMail = Nothing
Set OutApp = Nothing
End Sub[/FONT]
[FONT=Courier New]

Hi Thanks for your help with this, have put this code in and it works just fine apart from one small enhancement i need. The email addresses who will receive this file will vary from time to time and i don't want the users to have to go into the code to amend the addresses. I have a table which based on earlier queries will show the correct email address(s) to send the file to, is there any way that this section could be amended to refer to the email address in the table?

With OutMail
.To = "emialid"

Thanks again for your help

Cheers

 

[xenou]

Hi Thanks for your help with this, have put this code in and it works just fine apart from one small enhancement i need. The email addresses who will receive this file will vary from time to time and i don't want the users to have to go into the code to amend the addresses. I have a table which based on earlier queries will show the correct email address(s) to send the file to, is there any way that this section could be amended to refer to the email address in the table?

With OutMail
.To = "emialid"

Thanks again for your help

Cheers

What's the name of the table? What's the name of the field in the table with the addresses? Are there more than one address? If so, are they listed as a string one one cell or as separate emails in many cells? Consider providing sample of the data. Consider providing a sample of the query that gives you the email addresses. It's much easier to help that way. As your question stands, all that can be said is "yes, there is a way to amend the code to refer to the email addresses in the table".

 

[hcabs99]

What's the name of the table? What's the name of the field in the table with the addresses? Are there more than one address? If so, are they listed as a string one one cell or as separate emails in many cells? Consider providing sample of the data. Consider providing a sample of the query that gives you the email addresses. It's much easier to help that way. As your question stands, all that can be said is "yes, there is a way to amend the code to refer to the email addresses in the table".

Hi

The email table is called WMS_Emails . The field which contains the email addresses is called Addresses (seperate with . All the required addresses are in this field separated by a ;

The query to populate the required address(s) into this table is run on an earlier macro and doesn't need to be referred to in this code

Cheers

 

[pedie]

my apologies, was away for few days.
for example

[FONT=Courier New].To = Range("A1").value 'or Cells(1,1).value[/FONT]
[FONT=Courier New].CC = Range("B1").value 'or Cells(1,2).value[/FONT]

Or even Range("A1").value & ";" & Range("B1").value

 

[hcabs99]

my apologies, was away for few days.
for example

[FONT=Courier New].To = Range("A1").value 'or Cells(1,1).value[/FONT]
[FONT=Courier New].CC = Range("B1").value 'or Cells(1,2).value[/FONT]

Or even Range("A1").value & ";" & Range("B1").value

Hi. No problem at all

Not entirely sure what you mean here , how do i enter this when the email addresses i need are found in the following table / field please?

WMS_EMAILS / Addresses (seperate with

Cheers

 

[pedie]

not entirely sure how your table looks like.
please copy and paste your table in here....i want to see how your email addresses look like

 

[xenou]

You just get the address from your table:

Dim rs AS DAO.Recordset
CurrentDB.OpenRecordset("SELECT Addresses FROM WMS_Emails")
If Not rs.EOF Then
    With rs
        [COLOR="Red"]strTo[/COLOR] = rs.Fields("Addresses")
    End With
End if
rs.Close
set rs = Nothing

Now in your email code:

.To = [COLOR="Red"]strTo[/COLOR]

 

[hcabs99]

You just get the address from your table:

Dim rs AS DAO.Recordset
CurrentDB.OpenRecordset("SELECT Addresses FROM WMS_Emails")
If Not rs.EOF Then
    With rs
        [COLOR="Red"]strTo[/COLOR] = rs.Fields("Addresses")
    End With
End if
rs.Close
set rs = Nothing

Now in your email code:

.To = [COLOR="Red"]strTo[/COLOR]

HI, Sorry for the long delay in my response, have taken a well earned break!

So this code, do i incorporate this into the current Send attachment functionality, or do i create a new module. Sorry for the all the questions, i am learning VBA slowly!

Cheers