Email multiple attachments (forms as PDF's) (1 Viewer)

  • Thread starter Deleted member 146202
  • Start date
D

Deleted member 146202

Guest
Hi all,

I'm coming to the end of a project (I think), and for one of the final tasks I want to email two forms as pdf's with the click on a button (open it ready to press send anyway).

Context:
My database stores logistics information and from the main data-entry/viewing form you can press two buttons to see the data presented neatly in two other A4 size forms for printing/emailing. (to send to hauliers).

However, rather than going into these forms, clicking email, waiting, and then combining the PDF's into one email, I'd love some help on how VBA can allow me to send them both in one email with one click of a button.

Also, if there would be a way to combine the two PDF's into one?? that would be amazing... but I don't know if that's possible.

Edit:
I have a button ready that opens an email, and uses a dlookup to fill the recipient from a table, as well as creates the title from some table data too... but when I add an attachment it doesn't do the 'WHERE "[Order Number ] = " & [Order Number] type thing that I've come to expect. It opens all of the records in that form and so obviously it's useless. Also there's only space for one attachment in the style I've done it as.
 

bastanu

AWF VIP
Local time
Yesterday, 17:20
Joined
Apr 13, 2010
Messages
1,401
Can you please post the code you have? For reporting purposes and exporting to PDF you shouldn't really use forms but reports. You can easily convert them by using SaveAs and choose report in the options.

Cheers,
Vlad
 
D

Deleted member 146202

Guest
Hi,

Ok thanks for the tip, I've saved them as reports now.

It won't let me paste for some reason, but see below.

Code:
Dim varName as Variant
Dim varCC as Variant
Dim varSubject as Variant
Dim varBody as Variant

varName = DLookup("[Email Addresses]", "tblHauliers",, "[Haulier] =" & "[Haulier]")

varCC = "jack@jack.com"

varSubject = "Collection No S" & [Order Number]

varBody = "blah blah blah"

DoCmd.SendObject acForm, "frmSummarySheet", "PDFFormat(*.pdf), varName, varCC, varSubject, varBody, True, False
 

Cronk

Registered User.
Local time
Today, 11:20
Joined
Jul 4, 2013
Messages
2,770
If you click the Search towards the top, and type Send Multiple Attachments you will get a swag of threads where code is provided
 

bastanu

AWF VIP
Local time
Yesterday, 17:20
Joined
Apr 13, 2010
Messages
1,401
The easiest way to have them both in one pdf file is to have them both on one report (you can embed one as a sub-report at the end of the first one). I also suggest that you open them in design view and modify their record source to include a reference to the unique identifier of the current record on the "main data-entry/viewing form" in order to make them show only the appropriate info. Once you do these things you can continue to use your code (change the prefix of the report name from frm to rpt for consitency :)).

If you want to keep them as separate pdfs you will need to replace your emailing code with a custom function to automate Outlook instead of using Docmd.sendobject.
Here is an example of that function:
Code:
Function vcSendEmail_Outlook(sSubject As String, sTo As String, Optional sCC As String, Optional sBcc As String,Optional sBody As String, Optional sAttachment As String, Optional sAttachment1 As String, Optional sAttachment2 As String)

    Dim OutApp As Object
    Dim OutMail As Object
 
  
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
 
  
 
    Set OutMail = OutApp.CreateItem(0)
 
    OutMail.To = sTo
    If sCC <> "" Then OutMail.CC = sCC
    If sBcc <> "" Then OutMail.BCC = sBcc
    OutMail.Subject = sSubject
    OutMail.HTMLBody = sBody

    OutMail.Attachments.Add (sAttachment)
    OutMail.Attachments.Add (sAttachment1)
    OutMail.Attachments.Add (sAttachment2)


 
    OutMail.Display  'Send | Display
    Set OutMail = Nothing
End Function

To use it you would replace the docmd.sendobject line with a call to it providing the required arguments in the right order (you will need to save the two reports first as PDF files and then provide the full paths to the function).

Cheers,
Vlad
 
D

Deleted member 146202

Guest
Hi,

Thanks for that help, great stuff.

I think I'm going to combine them into one report and do it that way.

what do you mean about a unique identifier though? When you open it from my main form it does pull the individual record that you're looking at by doing that, just not when you run this code.
 

bastanu

AWF VIP
Local time
Yesterday, 17:20
Joined
Apr 13, 2010
Messages
1,401
I mean the Order Number or Order ID or whatever primary key you have defined for the table. You have to look at the reports and add that criteria to their record source, something like Forms![Main_Data_entry_form]![Order_Number]. I suspect that currently your two buttons provide that in the Where clause of the Docmd.OpenForm method.

Cheers,
Vlad
 
D

Deleted member 146202

Guest
I've decided to switch to the outlook automation version of creating a message.

Still struggling with making it only send out 1 record... Apologies but I'm not sure I'm grasping your unique identifier advise. The unique identifier is the order number, which is included in the tblmaster which is the record source of both the form I enter information into, and the form I' trying to send out via email. (went back to using a form as when I re-opened the database after switching to a report, it was screwed up, I may look at that again in the future.
 

bastanu

AWF VIP
Local time
Yesterday, 17:20
Joined
Apr 13, 2010
Messages
1,401
Try to save the form as a report with a different name. When you open the report in design view go to the properties and look at the record source property. If that is the entire table (tblmaster) you need to modify it to add criteria (a where clause) to limit the records to just the current record in the main form. So the new record source will be something like this: SELECT * FROM tblmaster WHERE [Order Number] = Forms![Main form]![Order Number];

You should actually save that as a query and simply reference the query in the record source property of the reports.
If you get stuck maybe you can make a copy of your db (empty the tables of any sensitive info) and upload it here so we could have a look.

Cheers,
Vlad
 
D

Deleted member 146202

Guest
Brilliant that makes total sense to me now. I'm going to have a look later (just got up). Let you know how it goes :)
 
D

Deleted member 146202

Guest
Great stuff, its working! :)

1 thing though, if you have any ideas... The Dlookup I posted above is always finding record 1 from the hauliers table, and putting that in the 'To' field in my email. Why is it not doing it's job correctly? :S
 

bastanu

AWF VIP
Local time
Yesterday, 17:20
Joined
Apr 13, 2010
Messages
1,401
Try varName = DLookup("[Email Addresses]", "tblHauliers", "[Haulier] ='" & [Haulier] & "'") if [Haulier] is text (or even better replace the single quote with two double quotes in case you have names with apostrophes - varName = DLookup("[Email Addresses]", "tblHauliers", "[Haulier] =""" & [Haulier] & """"). If the [Haulier] field is a number use varName = DLookup("[Email Addresses]", "tblHauliers", "[Haulier] =" & [Haulier])

Cheers,
Vlad
 
D

Deleted member 146202

Guest
Hi,

Without quotes it gives a data mismatch error. With only one quote it just choose the first in the table, as mentioned, and with 3 quotes it puts nothing in the 'To' field whatsoever.

Strange one :(
 
D

Deleted member 146202

Guest
My haulier field contains Semi Colons... Could that be anything to do with it?
 

bastanu

AWF VIP
Local time
Yesterday, 17:20
Joined
Apr 13, 2010
Messages
1,401
Check the name of the control on the main form that has Haulier as the control source. If haulier is text the version with double double quotes should work. Note that you have 3 double quotes at the beginning and 4 double quotes at the end.
 
D

Deleted member 146202

Guest
Think I've found the error. 'Haulier' in the Hauliers table is short text. Whereas in the master form it's a number (looked up). Kind of lost as to how to solve it.
 

bastanu

AWF VIP
Local time
Yesterday, 17:20
Joined
Apr 13, 2010
Messages
1,401
What is the name of the combo box on the main form? Does the Hauliers table contain the number as well (HaulierID perhaps)? In that case you use varName = DLookup("[Email Addresses]", "tblHauliers", "[HaulierID] =" & [ControlNameOnForm])

Cheers,
Vlad
 

bastanu

AWF VIP
Local time
Yesterday, 17:20
Joined
Apr 13, 2010
Messages
1,401
Glad to hear you got it working! I love Access too, started using it in 1996 -was called Access 2 :)

Cheers,
Vlad
 

Users who are viewing this thread

Top Bottom