VBA to send mail with Report

[Bunic000]

Good morning, I have been working with VBA in Access for a while now. I am a hobbyist but would like to learn a lot. I have already taken several (home) courses so I am not completely ignorant. But now to the point.
I have the code below where the code retrieves the email address from another form and attaches the report......but: If the email address is entered in Outlook then it says "test@adress.nl#mailto:test@adress.nl#". This way he does not send the email. I now have to manually remove #mailto:test@adress.nl# to be able to send the email. In addition, I need to have the form containing the email address "open", which is not desirable. It also loads all reports and not just the report linked by Control ID. Can/will someone explain to me how I can solve this and why things are not going well now? It's good if someone wants to change this, but please explain why things go the way they do because I really want to learn this.
Thanks for your help.

Form with mail: ContactFrm > Email
Form with button to mail: ControleArchief
Form with ID: ControleID
Report name: Rapport



DoCmd.SendObject acSendReport, "Rapport", acFormatPDF, Forms!ContactFrm!Email, , , _
"Keuringsrapport " & ControleID & "", "Bank payment", , False



Greetings Richard

[Welshgasman]

I take it when you say It also loads all reports, that it is for all records, not just the record for your required ID?

So you can refer to the control on the form in the query for the report, so that when the report opens it only ever has that particular record.
If that is not acceptable to you, then you will likely need to have a separate report just for one record?

I used to use Aoutlook automation and recordsets to identify particular records.

You could try opening the report in preview mode with the where clause.

I have just done that, then used the SendObject and I only get the required record?

docmd.OpenReport "rpttbldates",acpreview,,"StepsID=12"

[xps35]

We cannot see what is stored in the email field. If the displayed value is really there, you can edit it in your code (subtract a piece) before sending it to Outlook.

It is of course your own choice that the required data is on different forms. You can also create a form that contains everything you need.

When creating the report, you must apply a filter so that only the data for the relevant ID is shown.

I have built something similar myself. I'll get back to you tonight (Dutch time). I now work on a tablet.

[Bunic000]

Good afternoon, I had saved a code from a long time ago. I've been tinkering with this and it's starting to look a lot like this. I now "normally" see 1 email address (cause: in the table the Email box was in Hyperlink), so that is solved. Now it only gives an error when adding the report. Later I want to be able to send multiple reports. the error is indicated in the italicized and underlined text. I have also included the error message as an attachment. What's going wrong here!!It also continues to retrieve all reports instead of the report with the corresponding Control ID. It will take some time, but we are slowly getting there.



I use now the code:

Dim objOutlook As Object
Dim objMail As Object
Dim strReport1 As String, strReport2 As String

strReport1 = "Rapport"
strReport2 = "Rapport 2"



Set objOutlook = CreateObject("Outlook.Application")


Set objMail = objOutlook.CreateItem(0)


With objMail

.To = Forms!ContactFrm!Email

.Subject = "Keuringsrapport AED"

.Attachments.Add CurrentProject.Path & "" & strReport1 & ".pdf"
.Attachments.Add CurrentProject.Path & "" & strReport2 & ".pdf"

.Body = "Hierbij het keuringsrapport van de door ons gekeurde AED."

.Send
End With


Set objMail = Nothing
Set objOutlook = Nothing


MsgBox "E-mail is verzonden!"




Thank you in advance
Greetings, Richard

[Welshgasman]

I do not read German, so no idea as to what that error message is.
It is also up to you to create the pdf with the correct data, so that would be using the Where clause if I was doing it, exactly the same as I showed when you were using the SendObject method.

[xps35]

I do not read German

It is Dutch. Never call a Dutchman German

BTW: OP might also come from Belgium.

[xps35]

Add

Debug.PrintCurrentProject.Path & "" & strReport1 & ".pdf"

to your code to see what the real value of the string is.
Check with ctrl-g

[Bunic000]

Dear @welshman and @xps35 thanks for all the help. I am indeed Dutch . @xps35 unfortunately your code doesn't work if I copy your code 1 on 1 everything turns red. I have to start with a point. When I enter: [.Debug PrintCurrentProject.Path & "" & strReport1 & " .pdf] I get error 424 "object required"

[xps35]

Sorry, I forgot a space

Debug.Print CurrentProject.Path & "" & strReport1 & ".pdf"

[Welshgasman]

My apologies for getting the language wrong.

[Bunic000]

Good morning, sorry to be back. I no longer receive any error messages, but neither do I receive any attachments. It is just an email without attachments, what am I doing wrong?
Have a nice day
Greetings Richard

[Welshgasman]

Debug.print CurrentProject.Path & strReport1 & ".pdf"
Post back what you get.
Does the report exist there?

[xps35]

It is still difficult to judge that from a distance. We also don't see your current code.

I now also realize that creating the reports is done outside the procedure. It is better to do the creation and sending in one and the same procedure. Then you can at least be sure that the reports have been created.

[Welshgasman]

It is still difficult to judge that from a distance. We also don't see your current code.

I now also realize that creating the reports is done outside the procedure. It is better to do the creation and sending in one and the same procedure. Then you can at least be sure that the reports have been created.

Could even check to make sure the file exists? Use Dir()

[Bunic000]

I completely understand that it is difficult for you to judge this from a distance. I have created a link so that you can take a look at my database (test environment). I made up all the information such as names, address details, AED information.
When you open ContactFrm, you can click on an AED in the subform (serial number, name or type). It will then open another tab with the AED data. Next are dates (inspected and when again). If you click on this you will arrive at the inspection report. I have a button for printing and a button below that for emailing, everything works, but it opens all reports. And a test button that contains the code we are currently working on (several reports). I still have to change report 2 (still the same as the first).


I'd love to hear what you think. If you need more information, please let me know.

https://1drv.ms/f/s!ApUQg9-IqiS1h60B...tUd3Q?e=soqpSz




Greetings Richard