Yes basically, I like to be able to send each individual an email with an attached PDF file. As shown in report.jpg, force new page is set on an ID header, when I do a print preview, only each person's report are display on the screen. Now I just need to figure out how to send it via email.
Ok, I will write and test some code for you. It will take some time.
Great Thank you very much, anything you can do will help me a lot.
Ok, I am attaching a database in Access 2003 format that includes a module with this subprocedure:
You will notice that there are 3 places where you are required to add changes, properly commented.
Public Sub EmailReport()
Dim golApp As Outlook.Application
Dim gnspNameSpace As Outlook.NameSpace
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strSQL As String
Dim strFileName As String
Dim objMail As MailItem
Set golApp = New Outlook.Application ' Application object.
Set gnspNameSpace = golApp.GetNamespace("MAPI") ' Namespace object.
strFileName = "c:\YourFolder\YourFileName.pdf" 'Change this for your specific folder
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
strSQL = "SELECT Email FROM [instructor table]"
cmd.CommandText = strSQL
Set rst = cmd.Execute
While Not rst.EOF
Set objMail = golApp.CreateItem(olMailItem)
objMail.To = rst("Email")
objMail.Body = "This is the email body" 'Replace it by your body
objMail.Subject = "This is the email subject"
Set objMail = Nothing
Set rst = Nothing
1."strFileName = "c:\YourFolder\YourFileName.pdf" 'Change this for your specific folder
Replace the red part with the full path and filename of your report
2. objMail.Body = "This is the email body" 'Replace it by your email body
3. objMail.Subject = "This is the email subject" 'Replace it by the email subject
There is also a form in the database, with a button. Once you click the button, the emails will be sent, using the default email account in your Outlook.
Currently it works with Outlook 2013 and Access 2013. In order to get it to work with Office 2010, you can simply import all the form and the Visual Basic module into your database, open the module in the Visual Basic Editor, open the Tools->References menu, and uncheck the reference to the Microsoft Outlook 15 Object Library. You will then need to scroll the list down and check a reference to the Microsoft Outlook 14 Object Library.
This should get it working. The code uses a table name instructor table, which I believe is the name of the table that contains the email addresses you want to use.
As an alternative, you can create a new module yourself, with the code for the procedure I have posted here. You will need to add references to the Microsoft Outlook 14 Object Library and to the Microsoft ActiveX Data Objects 2.x Library (I did it for 2.8 library version).
Once you do this, you can run the code, the best way being similar to the way I used with the form in the database attached.
Wow, that was quick. thank you so much. I will test it tonight after my meeting, I'll will report back tomorrow to let you know the result.
I am getting a syntax error on one of the line, see attached. I think I got format incorrectly, can you take a look? ThanksAttachment 33543
Enclose the c:\... in double quotes "
did that too now I am getting "run time error 5" invalid procedure call and argument.
this is what I have, I've move the location of the file to root of drive C.
objMail.Attachments = "c:\instructor1.pdf"
You cannot do that. Look at the code I posted. The file name needs to be changed at the line that looked like this:
The line with the objMail.Attachments statement should not be changed and does need to be changed.
strFileName = "c:\YourFolder\YourFileName.pdf"
I send the file back, I added 4 test users with query and report this is how it would look like with the real data.
I did that at first, thats when I get the error so I move the file to c:\. The working folder is located on my desktop: c:\User\rho\Desktop\workshop score\instructor1.pdf
Ok, I have updated the file to use the document in c:\.
Ok thanks, I try it soon as I get back got another dame meeting
OK that works, how do I send just correct portion of the report to each person? The instructor1.pdf include all the pages in one file, that mean everyone the the same file with everybody else's information on it. Its there a way to send just the pages for that person? Example: if Larry only has two pages, how do I send him just that two pages out of said 90 pages. On the report if a "force new pages before section" is added to the ID header, then on the print preview, it will display the report by each person's ID not as one continues report as with instructor1.pdf.