lehi52
asked on
Merge fields in database fields, onto one section to send email
I attached a sample database. You will see the send email button on the contact form. When I click it it opens a box that allows me to choose which email to send. It will then automatically fill in the name, subject and text into my default email program. The one thing I need is the ability to send an email with different fields automatically going into that email. I would need things like EPLI Total, Connam Name along with a big long text merged into a single email.
1.accdb
1.accdb
try this codes
Private Sub Command231_Click()
On Error GoTo Command231_Click_Err
Dim StrBody As String
StrBody = "Dear " & Me.Connam_Name & vbCrLf & vbCrLf '& "Here you will find the attached report......"
StrBody = StrBody & "Total " & Me.EPLI_Total & vbCrLf & vbCrLf
DoCmd.OpenForm "frmSelectTemplate", , , , , acDialog
StrBody = StrBody & DLookup("BodyText", "tblBodyTemplate", "TemplateID=" & Form_frmSelectTemplate.Tem plateID)
DoCmd.Close acForm, "frmSelectTemplate"
DoCmd.SendObject acReport, "actions and opps", "PDFFormat(*.pdf)", Me.[Connam Email], , , "Action and Opportunities", StrBody
Command231_Click_Exit:
Exit Sub
Command231_Click_Err:
MsgBox Error$
Resume Command231_Click_Exit
End Sub
Private Sub Command231_Click()
On Error GoTo Command231_Click_Err
Dim StrBody As String
StrBody = "Dear " & Me.Connam_Name & vbCrLf & vbCrLf '& "Here you will find the attached report......"
StrBody = StrBody & "Total " & Me.EPLI_Total & vbCrLf & vbCrLf
DoCmd.OpenForm "frmSelectTemplate", , , , , acDialog
StrBody = StrBody & DLookup("BodyText", "tblBodyTemplate", "TemplateID=" & Form_frmSelectTemplate.Tem
DoCmd.Close acForm, "frmSelectTemplate"
DoCmd.SendObject acReport, "actions and opps", "PDFFormat(*.pdf)", Me.[Connam Email], , , "Action and Opportunities", StrBody
Command231_Click_Exit:
Exit Sub
Command231_Click_Err:
MsgBox Error$
Resume Command231_Click_Exit
End Sub
ASKER
Im not seeing how to get the data from one record to merge into the table.
did you test to send email from the Introduction template ? it will give you the date and phone from current record.
ASKER
I did the test send, but it did not merge into the email. It just shows {Eff Date} and {Connam Phone} without it merged. It is going to Lotus notes. Could that be why?
did you add my functions ?
Function ConvertBody(BodyText As String) As String
Dim strFullBody As String, strBody As String
Dim strPos As Long, endPos As Long
Dim FieldName As String
strFullBody = BodyText
strPos = InStr(BodyText, "{")
Do
endPos = InStr(strFullBody, "}")
strBody = Left(strFullBody, strPos - 1)
FieldName = Mid(strFullBody, strPos + 1, endPos - strPos - 1)
ConvertBody = ConvertBody & strBody & Me.Controls(FieldName)
strFullBody = Right(strFullBody, Len(strFullBody) - endPos)
strPos = InStr(strFullBody, "{")
Loop Until strPos = 0
ConvertBody = ConvertBody & strFullBody
End Function
and then change the code before sendopbject DoCmd.OpenForm "frmSelectTemplate", , , , , acDialog
strBodyText = DLookup("BodyText", "tblBodyTemplate", "TemplateID=" & Form_frmSelectTemplate.TemplateID)
DoCmd.Close acForm, "frmSelectTemplate"
strBody = strBody & ConvertBody(strBodyText)
ASKER
Where do I add a function? I understand the other code part.
ASKER
I downloaded the sample and it shows the {Eff Date} in brackets still not filled in. Do I need to do anything else?
ASKER
SHould I download the sample and add the function to it. Or is the function already in their?
it should be there. you can check for it.
i now added on tab "page232" a test button, you can test it there.
Which version MS Access are you using ?
4.accdb
i now added on tab "page232" a test button, you can test it there.
Which version MS Access are you using ?
4.accdb
ASKER
2007
<I would need things like EPLI Total, Connam Name along with a big long text merged into a single email. >
how do you want the merging done? where do you want the EPLI Total, Connam Name, etc to appear in the body of the mail?
post a sample result.
how do you want the merging done? where do you want the EPLI Total, Connam Name, etc to appear in the body of the mail?
post a sample result.
ASKER
Gozreh, The test seems to work but when I click the button to send to my email it pulls a different text. Here is the database.
5.accdb
5.accdb
ASKER
Rey, There is a table in my database. See the sample. I want to be able to put the merged data into a place where I need it. We have not written the emails yet. But we want need the ability to say we want EPLI total in this place or that place in the body text of the tbl where the email text is.
what do you mean
it pulls a different textWhich text are you getting ?
ASKER
The test pulls in text and when I send it to email its different. Here is the test converter:
Here is the sample email
I want to send and email to everyone to see if that will work for us. I want to send an email to you. Too I want to send and email to everyone to see if that will work for us. I want to send an email to you. TooI want to send and email to everyone to see if that will work for us. I want to send an email to you. Too I want to send and email to everyone to see if that will work for us. I want to send an email to you. Too I want to send and email to everyone to see if that will work for us.
I want to send an email to you. 3/20/2014 Too I want to send and email to everyone to see if that will work for us. I want to send an email to you. Too I want to send and email to everyone to see if that will work for us. I want to send an email to you. Too I want to send and email to everyone to see if that will work for us. I want to send an email to you. Too I want to send and email to everyone to see if that will work for us. I want to send an email to you. Too I want to send and email to everyone to see if that will work for us. I want to send an email to you. Too I want to send and email to everyone to see if that will work for us. I want to send an email to you. Too I want to send and email to everyone to see if that will work for us. I want to send an email to you. Too I want to send and email to everyone to see if that will work for us. I want to send an email to you. Too I want to send and email to everyone to see if that will work for us. I want to send an email to you. Too I want to send and email to everyone to see if that will work for us. I want to send an email to you. Too 123
Here is the sample email
Here you will find the attached report......3/10/2014 Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum. This is a test.
d k fdjf dj jdfjdjkfjdkfjkdjfkjdfjkdj fd fdj fdjf djfdjfjdkjfdkj fd fjd jfdjfkdjfkdjf d fdjfjdkjfdkjfkdjkfj dkj fdjkfjdkjfdjkfj dkjf kdj fjdkjfkdjfdj jfdjfdjfkdjfkdj kjd fjdkjfdjfjd jfjdkf jdkjfkd jdkfj
jdkfj dkjfk dfkjkdjkfdjfjd jdjjfdjfj
test the Email button next to "Action and Opportunities" - on tab "Reporting"
dos it work there ?
dos it work there ?
ASKER
Weird. That does work. Haha. So now I need to get that code to the General Tab and make it work on the Send email button.
so its my mistake, in the first place i only added the function to the "Action and Opportunities",
i didn't recognize that you added a button Send Email on the General tab.
so now its working as you needed ?
i didn't recognize that you added a button Send Email on the General tab.
so now its working as you needed ?
ASKER
It is close. I just need to get the code onto the button on the General Tab.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No doubt that Gorzeh is one of the best on here. He has helped me a lot.
i added 2 fields in the Introduction record {Eff Date} and {Connam Phone}.
2.accdb