Send email from access with the data includes in the record set

**Brilli** · 12-15-2023, 11:36 AM

Hi All,
i need to send by outlook mail the content of a record in the database access. i have tried this code

Private Sub Comando29_Click()

' https://youtu.be/6fu0kXXU_R4 - MANDARE EMAIL CON ACCESS

Dim OutApp As Object
Dim OutMail As Object
Dim strMsg As String
Dim strDest As String
Dim strAllegato As String

Set OutApp = CreateObject("outlook.application")
Set OutMail = OutApp.CreateItem(0)

strMsg = "<!DOCTYPE html PUBLIC '-//W3C//DTD HTML 4.01//EN' 'http://www.w3.org/TR/html4/strict.dtd'>"
strMsg = strMsg & "<html>"
strMsg = strMsg & "<head>"
strMsg = strMsg & " <meta content='text/html; charset=ISO-8859-1'"
strMsg = strMsg & " http-equiv='content-type'>"
strMsg = strMsg & " <title></title>"
strMsg = strMsg & "</head>"
strMsg = strMsg & "<body>"
strMsg = strMsg & "<span style='font-weight: bold;'>Spett.</span><br>"
strMsg = strMsg & "<br>"

strMsg = strMsg & "i dati sono:<br>"
strMsg = strMsg & "<br>"
strMsg = strMsg & "<table style='text-align: left; width: 100%;' border='1'"
strMsg = strMsg & " cellpadding='2' cellspacing='2'>"
strMsg = strMsg & " <tbody>"
strMsg = strMsg & " <tr>"
strMsg = strMsg & " <td>ffff</td>"
strMsg = strMsg & " <td>fff</td>"
strMsg = strMsg & " <td>fff</td>"
strMsg = strMsg & " <td>fff</td>"
strMsg = strMsg & " </tr>"
strMsg = strMsg & " <tr>"
strMsg = strMsg & " <td><p align='center'><strong>" & Format(DESCRIZIONE_ELABORATO, "Standard") & "</strong></p> </td>"

' Ottieni il percorso dell'allegato
strAllegato = CurrentDb.OpenRecordset("02_TAB_REV").Fields("Prog ettazione").Value

' Aggiungi l'allegato alla email
OutMail.Attachments.Add FileName:=strAllegato

strMsg = strMsg & "</tbody>"
strMsg = strMsg & "</table>"
strMsg = strMsg & "<br>"
strMsg = strMsg & "<br>"
strMsg = strMsg & "saluti,<br>"
strMsg = strMsg & "</body>"
strMsg = strMsg & "</html>"
strDest = txtEmail ' variabile email

With OutMail
.to = strDest
.cc = ""
.bcc = ""
.subject = "scad pag"

.HTMLBody = strMsg
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing

End Sub

but it gives me an error.... why?
could you help me?

thx

**orange** · 12-15-2023, 11:52 AM

What is the error number and message?

**Welshgasman** · 12-15-2023, 11:54 AM

You need to add the fields of the recordset into the body of your email.
Attachments are literally that .....Attachments. You would use these to attach a file like a report.

**Brilli** · 12-18-2023, 04:52 AM

Originally Posted by orange

What is the error number and message?

runtime 13

line error:

strAllegato = CurrentDb.OpenRecordset("02_TAB_REV").Fields("Prog ettazione").Value

**Brilli** · 12-18-2023, 04:56 AM

Originally Posted by Welshgasman

You need to add the fields of the recordset into the body of your email.
Attachments are literally that .....Attachments. You would use these to attach a file like a report.

i want to attach file loaded in the db access, in a recordset of access, not an external one or send a report. :..(

**Micron** · 12-18-2023, 09:02 AM

I don't think you can open a recordset and get a field value in the same line? AFAIK, you have to open first then refer to the rs field. With that you will get one value, which seems odd to build an html table for that. BTW, your table code is missing an end tag for the 2nd table row (at least I can't see it).

Please use code tags (# icon on posting toolbar) for code, not comment tags, and maintain indentation in your code. Also, when asked for the error message please provide it or at least a close proximity. There are thousands of error numbers and we shouldn't have to look them up. You'd think that with any experience these should be memorized, but it is the opposite. The more you practice the less you see these errors so the meaning of the numbers is easily forgotten.

**Brilli** · 12-19-2023, 01:16 AM

Originally Posted by Micron

I don't think you can open a recordset and get a field value in the same line? AFAIK, you have to open first then refer to the rs field. With that you will get one value, which seems odd to build an html table for that. BTW, your table code is missing an end tag for the 2nd table row (at least I can't see it).

Please use code tags (# icon on posting toolbar) for code, not comment tags, and maintain indentation in your code. Also, when asked for the error message please provide it or at least a close proximity. There are thousands of error numbers and we shouldn't have to look them up. You'd think that with any experience these should be memorized, but it is the opposite. The more you practice the less you see these errors so the meaning of the numbers is easily forgotten.

ok sorry fow #..... could you show me the code to do what you say?
thanks...

**June7** · 12-19-2023, 01:29 AM

Open a recordset object:

Dim rs as DAO.Recordset
Set rs = CurrentDb.OpenRecordset("02_TAB_REV")

Refer to fields of recordset wherever needed - with concatenation to combine with static strings:

strMsg = strMsg & " <td>" & rs("Prog ettazione") & "</td>"

What do you mean by "file loaded in the db access"? If you mean a file like Excel spreadsheet, Word doc, or image saved in Attachment field in table - the above won't work. The file must first be saved out to folder location then pulled in to the Attachment property of email by referencing that folder/file path. Review https://learn.microsoft.com/en-us/of...ile-method-dao

Also, strongly advise not to use spaces in naming convention.

**Brilli** · 12-19-2023, 03:04 AM

ok... so i can't send attach file in db directly from db.... so how i can save in the same folder the file and then take it to send by emal and delete it?
i think the procedure can be only:

1- click on a button to save file in the same directory of Access DB
2- take it or them from the folder and attach them in a mail

could help me to do this?

**Welshgasman** · 12-19-2023, 03:10 AM

Need to get your terminolgy correct.
As it stands, you are trying to email the value of one field?
That can easily be done as @June7 has shown.

So no real need for an attachment in this case.
Plenty of examples on the net on how to attach files to an outlook email on the net.
If it was a set of records in a query, investigate SendObject command.

**Brilli** · 12-19-2023, 09:20 AM

i want to take the file into access db record.
i have tried this example but i have had run time error. i try again.

thx

**Welshgasman** · 12-19-2023, 09:28 AM

i want to take the file into access db record.

I do not understand what you are saying?
Tell us what the error is, show us your latest code (within code tags)

It is called a language for a reason. You cannot just make up words yourself.

There is a structure to the language and the objects.

**Gicu** · 12-19-2023, 10:46 AM

As you say in post #9 here are the steps you need to take:

1.Save the attachment to local folder (I recommend using the same folder the front-end is in, you get that by using CurrentProject.Path). Here is a link to accomplish this step:https://learn.microsoft.com/en-us/of...ile-method-dao
2.Modify your emailing code to use the Attachments collection of the email message object to add the newly downloaded file. Here is a link to help you with this step:https://stackoverflow.com/questions/...-as-attachment
3. Delete the newly created file by using the Kill command: https://stackoverflow.com/questions/...-a-file-in-vba

Cheers,

**June7** · 12-19-2023, 01:06 PM

Did you even look at the link referenced in post 8?

Send email from access with the data includes in the record set

Thread Tools

Send email from access with the data includes in the record set

Similar Threads

Send email through access using data from record set

Access 2010 Send Outlook Email from Button Click - Failing to send email unless Outlook is open.

How To Send Bulk Email With There Relevant Data From Ms Access Table, Access 2007

Add record and send email on same click of button in form in Access

Can I send an email from access that takes some data from a report

Posting Permissions