Solved

How to get subject to merge automatically into email

Posted on 2014-02-06
13
405 Views
Last Modified: 2014-02-19
How do I get the subject to merge into this access database.  See the attached database.  SOmeone was helping me get it all set up,  but the one thing that is missing in the code is auto filling in the subject from the tblbodytemplate table.  I think I need to add a subject column in the table.  

It works by click the send email button which merges into my default email program.
1.accdb
0
Comment
Question by:lehi52
  • 7
  • 6
13 Comments
 
LVL 10

Expert Comment

by:Gozreh
ID: 39839755
Yes, you should add new field "Subject", then you will need to add in the subject  
DLookup("Subject", "tblBodyTemplate", "TemplateID=" & Form_frmSelectTemplate.TemplateID)

Open in new window

0
 

Author Comment

by:lehi52
ID: 39839777
I added subject and included Body text and it pulled an error. I need it to pul both the subject and the body text into that email.  
DLookup("Subject", "BodyText", "tblBodyTemplate", "TemplateID=" & Form_frmSelectTemplate.TemplateID)

Open in new window

0
 
LVL 10

Accepted Solution

by:
Gozreh earned 500 total points
ID: 39839794
No, Dlookup can only retrieve one expr at ones
this the Dlookup syntax  DLookup(expr, domain [, criteria] )
you can read here http://office.microsoft.com/en-us/access-help/dlookup-function-HA001228825.aspx
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:lehi52
ID: 39839799
That is good to know.  I will probably ask another question then on how to automatically pull the subject with the body text.
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39839802
Your code here should be
   Dim strBody As String, strBodyText As String
   Dim strSubject As String
   strBody = "Dear " & Me.Connam_Name & vbCrLf & vbCrLf & "Here you will find the attached report......"
   
   DoCmd.OpenForm "frmSelectTemplate", , , , , acDialog
   strBodyText = DLookup("BodyText", "tblBodyTemplate", "TemplateID=" & Form_frmSelectTemplate.TemplateID)
   strSubject = DLookup("Subject", "tblBodyTemplate", "TemplateID=" & Form_frmSelectTemplate.TemplateID)
   DoCmd.Close acForm, "frmSelectTemplate"
   strBody = strBody & ConvertBody(strBodyText)
   
   DoCmd.SendObject acReport, "actions and opps", "PDFFormat(*.pdf)", Me.[Connam Email], , , strSubject, strBody

Open in new window

0
 

Author Comment

by:lehi52
ID: 39839846
This code did not pull in the subject.
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39839865
did you remove the report "actions and opps" or renamed it ?
0
 

Author Comment

by:lehi52
ID: 39839866
I renamed it.  to Action to do. But what if I wanted another option to also remove the attachment.
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39839885
Like Scott McDaniel answered you in previous question http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28357659.html
that if you want to attach other pdf's and not from your reports, then you should use the outlook object instate of using the sentopject.
0
 

Author Comment

by:lehi52
ID: 39839887
Gozreh,   I mean to just not include an attachment at all.  Sorry I wasnt clear on that.
0
 

Author Comment

by:lehi52
ID: 39839895
I tried to remove that line of code and the button click didn't work so I wasnt sure what I was doing wrong.

I guess this is another question which I can ask elsewhere if you prefer. Mainly here I would like to get the subject line inserting.
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39839931
to send the report "Actions To Do" you can use this:
   DoCmd.SendObject acReport, "Actions To Do", acFormatPDF, Me.[Connam Email], , , strSubject, strBody

if you need to send email without report attached you should use this:
   DoCmd.SendObject acSendNoObject, , , Me.[Connam Email], , , strSubject, strBody

please check all option you can do with the SendObject method http://msdn.microsoft.com/en-us/library/office/ff197046.aspx

you can also see their on bottom of the page how to send email using outlook objects
0
 

Author Comment

by:lehi52
ID: 39839971
The subject line does merge now.  Gorzeh thank you.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question