Access 2010 - VBA Code - Add Report To Body Of Email?
Feb 25, 2013
Access 2010 - add report to body of an email
i have VBA code to create an email and attach an excel file, what i would like is code to add a report called REPORTMISSINGDATES to the body of the email.
this is the code i have so far, which works and adds everythng i wqant except the main body of the email.
Private Sub Command31_Click()
On Error GoTo Command31_Click_Err
Dim Email As String
Dim name As Variant
Dim EMPloy As Variant
Dim month As Variant
month = MonthName([Forms]![STAFFATTENDANCEMenu]![StaffMonth])
EMPloy = Forms!staffattendancezone!Staff
name = DLookup("[STAFFNAME]", "[QRYSTAFFNAME]", "[ASA] = Forms!staffattendancezone!Staff")
Email = (Forms!STAFFATTENDANCEAdjust!Email)
DoCmd.SendObject acQuery, "STAFFATTENDANCEZONECheckEmployee", "ExcelWorkbook(*.xlsx)", [email], "", "", "" & "Attendance Errors", "THIS IS WHERE I WANT THE REPORT TO GO" _
End Sub
I've set up a piece of VBA code to loop through and email various recipients data, in the form of records within the email body. This works fine, but occasionally produces an error whereby the email output body lines are duplicated spuriously. This only seems to happen when running a large number of email loops.
The data source for the email body content is a table, which for each 'loop' is refreshed with new data by 1) deleting records from that table, and then 2) appending data to the table.
The portion of code for the loop & email:
Do 'Build outputbodytext DoCmd.OpenQuery ("EmailQ2") DoCmd.OpenQuery ("EmailQ1") 'set up email Set rec2 = CurrentDb.OpenRecordset("outputbodytext") strSendTo = rec2("Email")
I can create PDF's, DOCX's retaining the PDF formatting save them back to different network locations and then retrieve and attach to emails that are generated for different areas, however I have been asked to scrap the attachment and insert the content of the attachment directly into the body of the email. I have had a good browse around the net with no real approach. The code below simply gets the recepients email address adds the subject and then is looking for the .HTMLBody which simply comes through in the body as
"O:divAKLResgroupE - ReportsHTML FilesDRAFT TEST .html".
I am trying to work my way into putting the content of my report into the body of an e-mail. I have used the following code to do so but I keep on getting the error: "An Expression You Entered Is the Wrong Data Type for one of the Arguments"
Code: Private Sub Command278_Click() On Error GoTo ER Dim db As Database Dim rstOpenPurchaseOrders As Recordset Dim strSQL As String
With this very simple command, I have the ability to send emails to several clients, reporting many things, quotations etc.
Private Sub Command84_Click() DoCmd.SendObject , , , , , , "SOMETHING", [SalesGroupingField] & "" & [1], True, "" End Sub
Now, i want, on the body of the emai, to display many records, it is from Nothwind, how can i display on the body of the email all records from this report?
I'm running the following code to generate an email from a report.
Quote:
Function ExportHTML3() Dim strline, strHTML Dim OL As Outlook.Application
[Code].....
But I don't really know much VBA and I found that code on the internet, so I can't figure out how it's doing that and if I can stop it. Or is there another way to get the text from the HTML file into the Email body, which brings the bold formatting with it, like the following.
I have a database to send e-mails from access trough Outlook. The issue is that I would like the body of the e-mail to have hyperlinks, colours, bolds and that I can get it from plain text.
This is the code I have:
Dim MyDB As Database Dim MyRS As Recordset Dim MyTable As Recordset Dim objOutlook As Outlook.Application Dim objOutlookMsg As Outlook.MailItem Dim objOutlookRecip As Outlook.Recipient Dim objOutlookAttach As Outlook.Attachment
[Code] ....
How can I make the body to be HTML format, or, can I have a e-mail template storaged in my PC and then indicate to use it whenever the e-mail is sent?
I want to automate an email to include the senders email addres with some text in the body of the email.
Code: Private Sub send_mail_Click() Dim olApp As Object Dim objMail As Object On Error Resume Next 'Keep going if there is an error Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open
Could you please help me and advise if it's possible to send report from MS Access by mail - having report as a content of an e-mail instead of attachement?
I would like to update the following code I have been using to send emails, from Access 2010 via Outlook 2010, to include my signature block with hard returns in between each line and a few above the signature.
Lets say the signature would be: My Name My Company Name My phone number My Reference
I tried next to: stMessage = Me![EmailMessage] &"My Name" & Chr(10) & Chr(13) & "My Company Name"
etc.
It does not work..
Here is the coding :
Code: Private Sub SendEmail_Click() On Error GoTo ProcErr Dim stLinkCriteria As String
I'm attempting to use Access to automate reporting emails and I'm trying to identify the best way to identify if the report is NULL (blank).
VBA: (strReportName is set to the report. Employees is set to the Employee Name. Email is set to the employees email address. [Requested By] is the column in the report that is used as a filter. The code is functional but also emails reports that are blank. I'm trying to just send the ones that have data) ...
We are currently using Access 2007 and I've got a database that sends emails out to multiple users depending on the data that was entered. We are about to upgrade to Access 2010 and I am testing it now. I was originally getting runtime error '287' when I was adding addresses to a Recipient object. I got past that by adding the addresses in a string and concatenating. Now I'm getting the error with .Send.
I created a Tracking System and would like to send an automatic reminder to the person the ticket has been assigned to, using the due date and the text message in the field "reminder message". Once the message send, I want the database to recognize and not send it again. I want the database to send a new message if the due date has changed.
I've been using the following code successfully in Access 2003 & now I need to migrate to Access 2010. The purpose of the code is to use the items that the user selects in the list box to build the criteria of a query. Access 2010 keeps giving me a syntax error when I try to run the query & I don't know why:
My code is:
On Error GoTo Err_Command151_Click
' Declare variables Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim varItem As Variant Dim strCriteria As String Dim strSQL As String
[Code] .....
The syntax error I get in Access 2010 is:
Syntax Error in query expression 'SELECT * FROM qryContractListSummarybyDateContract3TYPEBREAK WHERE qryContractListSummarybyDateContract3TYPEBREAK.Rep ortableName IN('Adbri Masonry NSW');'
I'm working on a vacation/time off tracker and was hoping to use the collect data feature. I've created a form where a user submits their requested date and number of hours, at which point I'd like the form to be sent to their manager for approval. The manager's email address is stored in a table. Once the manager marks yes or no, I'd like that reflected in the time off requests table, and an email sent to the requester letting them know if it was approved or not. Is this possible? How would I go about it. I'm pretty new to Access, learning on the fly, but I'm pretty good at modifying code to match my situation/working backwards if you have any examples for me to look at.
I would like to automate the current process of sending an e-mail with a PDF attachment that is unique to each recipient; it is their test results. I'm sending an e-mail one at a time (which was fine when the number of folks being tested was small, but now I'm up to approximately 200 e-mails and growing each year)
System Environment:
Windows 7 SP1 (64 bit) Office 2010 Professional
Access environment and Process:
1. A qry object/letter is built based on 4 tables (test results, result averages, a global employee list and location)
2. The Report has both static and dynamic text. Examples include: "Dear" [qry-Letter. F_name] and Address Information
a. =[qry-Letters.F_Name] & " " & [qry-Letters.L_Name] b. =[qry-Letters.Position] c. ="Dept " & Right([qry-Letters.Dept],3) & ", " & [qry-Letters.City]
3. The report also includes the employee's results, the session averages (testing is conducted at multiple locations each occurrence is a session) and companywide averages.
4. Report Process: Run the report, entering primary key Emp_ID, report is populated saved (overwritten with the next Emp_ID and printed as a saved PDF.
5. MS Outlook: create a new e-mail, text body is common to all e-mails e.g. "attached are your test results" the PDF is attached and the e-mail sent to the recipient (the e-mail address follows the corporate standard, =Trim([qry-Letters.F_Name] & "." & [qry-Letters.L_Name] & "@abc.com")
I currently have a database set up with three basic forms:
Form 1 = Main menu with options to go to Form 2 and Form 3
Form 2 = Employee information form which includes email address
Form 3 = Employee document upload form
My goal is once the Employee is registered in Form 2 they can then upload a document in Form 3. When they upload this document and fill out other parameters Including their bosses name (which is captured as a record in Form 2) they click "Submit Form." I would like for this submit form button to populate an email that is updated to send to the selected "Bosses name"
Currently on the Submit form button I have:
Private Sub Submit_Record_Click() DoCmd.SendObject _ , _ , _ , _ "email .com", _ <-- this is what I want to autopopulate with the correct persons email (as well as their name below) , _ , _ "***A new Lab Report has been submitted for your review***", _ "Bosses name," & vbCrLf & vbCrLf & vbCrLf & "Please log into the Report Database and review the latest pending report. If you have any questions please contact the sender." & vbCrLf & vbCrLf & "This is an automated response generated from Microsoft Access." & vbCrLf & vbCrLf & vbCrLf & "Sincerely," & vbCrLf & "ESBU Lab Report Database", _ False DoCmd.Close End Sub
My company recently upgraded our MS Office from 2007 to 2010 (except for Access).
Previously, when I had Access 2007 and Outlook 2007, I had a process that generated 50+ dynamic emails from an Outlook template file (.oft).
The code would loop through a listbox and replace the template's default text to a string of text specific to the selection in the listbox by utilizing the Replace() function on the MailItem .HTMLBody.
Since the upgrade to Outlook 2010, the code is able to run, however, the Replace() function is no longer working; Instead, each email that is generated maintains the template's default text.
The only thing that is not working is the Replace() function, all other aspects of the code work fine.
I've provided a simplified version of the code below:
Dim myOlApp As Outlook.Application Dim objMailMessage As MailItem Dim stBody As String Set myOlApp = Outlook.Application Set objMailMessage = myOlApp.CreateItemFromTemplate("C:UsersDesktop emplate.oft")
[Code] .....
I've recreated the template file in Outlook 2010, thinking that the template created with Outlook 2007 would be the culprit, but to no avail.
What could have changed from Outlook 2007 to Outlook 2010 that would render my previously valid code ineffective?
Are there certain references I need to enable in both Access and Outlook to allow VBA in Access modify the content in an Outlook email?
I almost have what I need, but I am not quite there. I am a VBA novice and need help. I couldnt understand some of the code I have searched for on this topic.
I have a query that returns exactly the results I want. By using a SendObject macro I can accomplish sending the message with a .txt file attached. What I want to do is instead of having the attachment I want to send my query "FaxReportQueryReport" in the body of the email. No set address, just a set RE: line of "Today's Deliveries".
I have a query table in Access 2007 with requests pending an individuals review and approval. I would like to create an automatic email each day to that individual which includes the actual query table impeded as part of the body of the email (not as an attachment).
I'm trying to put multiple records of data from a subform in a single e-mail, and my Outlook calendar. I have a contracting business, and on my Orders form (which details a single client), I have a sub-form that lists all the items to be done at that client's home (a different record for each work item). When I send an e-mail, or post the appointment to the Outlook calendar, I am trying to list all those records in the body of the e-mail, and/or in the Notes section of the appointment. I created the query to call the records, but have not figured out a way to use it in the code.
