Solved

How to get subject to merge automatically into email

Posted on 2014-02-06
13
412 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

632 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