Email Distribution & Text from Form (1 Viewer)

seneca1

New member
Local time
Today, 08:40
Joined
Jun 18, 2018
Messages
8
Hello!

I've been trying to sort this out, but haven't been able to make it work. Pretty green on VBA, so bear with me.

I have a form, and technicians use it to enter call information. I was able to put it all into a report, which could be done by sendobject (although I'm not able to quite get that working right, which probably shows how green I am). My desired end goal would be to create text email that would populate with the correct fields in an outlook email for only the current record viewed in the form.

For example (brackets indicate Field Name)

"Hello,

We have received a [Call_Type] call on [Initial_Call_Date] concerning [First] [Last].
The Vendor is [Vendor].
The reporting party is [Reporter Name] ([Reporter_Type]), who can be reached by [Reporter_Phone].
Phone call summary is:
[CaseLog_Initial]

Our ongoing phone call summary is:
[CaseLog_Ongoing]

The call is considered [Status]."


I also have another email distribution list, that I'd like to keep dynamic. The list is under a table titled "Email_List", and just has an ID and Email field.

Table is titled "Calls"
Form is titled "Frm-ALL"

Any help with a VBA code that could create the email would be appreciated. Is there a straight forward way to code this?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:40
Joined
Aug 30, 2003
Messages
36,118
You can build a string variable for the email:

strBody = "We have received a " & Me.[Call_Type] & " call on..."

You can include vbCrLf outside the quotes for a new line.
 

seneca1

New member
Local time
Today, 08:40
Joined
Jun 18, 2018
Messages
8
Perfect. What other code would I need to link it to an outlook email, and then have it pull in the email addresses from the email distribution list?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:40
Joined
Aug 30, 2003
Messages
36,118
SendObject is probably the simplest way to send an email. If you need to pull multiple addresses from a table, you can loop it and build a string. Here's my template code for a recordset loop:

Code:
  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT * FROM TableName"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF

    rs.MoveNext
  Loop

  rs.Close
  set rs = nothing
  set db = nothing
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:40
Joined
Aug 30, 2003
Messages
36,118
Oh, within the loop:

strAddresses = strAddresses & rs!EmailAddressFieldName & ","
 

seneca1

New member
Local time
Today, 08:40
Joined
Jun 18, 2018
Messages
8
Ok, have the email message working.

How do I fit in the email addresses within this code? Specifically so it will populate from the Email_List table?

Code:
Private Sub Command51_click()

Dim varName As Variant
Dim varCC As Variant
Dim varSubject As Variant
Dim varBody As Variant

varName = [ADDRESS]
varCC = " " 

varSubject = "Call Report - New - " & Me.[Regional_Center] & " - " & Me.[Consumer_Last] & ", " & Me.[Consumer_First]
'Email subject

varBody = "Hello," & vbCrLf & vbCrLf & "We have received a " & Me.[Call_Type] & " call on " & Me.[Initial_Call_Date] & " concerning " & Me.[Consumer_First] & " " & Me.[Consumer_Last] & "." & vbCrLf & "The Regional Center is " & Me.[Regional_Center] & "." & vbCrLf & "The reporting party is " & Me.[Reporter_Name] & " (" & Me.[Reporter_Party] & "), who can be reached by " & Me.[Reporter_Phone] & "." & vbCrLf & vbCrLf & "Phone call summary is: " & vbCrLf & Me.[CaseLog_Initial] & vbCrLf & vbCrLf & "Our ongoing phone call summary is:" & vbCrLf & Me.[CaseLog_Ongoing] & vbCrLf & vbCrLf & "The call is considered " & Me.[Status] & "."

'Body of the email
DoCmd.SendObject , , , varName, varCC, , varSubject, varBody, True, False
'Send email command. The True after "varBody" allows user to edit email before sending.
'The False at the end will not send it as a Template File
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:40
Joined
Aug 30, 2003
Messages
36,118
The loop in post 4 populating a variable like in post 5.
 

seneca1

New member
Local time
Today, 08:40
Joined
Jun 18, 2018
Messages
8
My current one has the email address for variable Name. I'm a little lost on how to rewrite the script so it'll integrate the two features.

(Thanks for your help by the way)
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:40
Joined
Aug 30, 2003
Messages
36,118
Replace

varName = [ADDRESS]

with

Code:
  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT * FROM TableName"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF
    varName = varName & rs!EmailAddressFieldName & ","
    rs.MoveNext
  Loop

  rs.Close
  set rs = nothing
  set db = nothing

For clarity I'd move the declarations to the top with the others and the cleanup stuff to the end (typically in an error handler).
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:40
Joined
Aug 30, 2003
Messages
36,118
De nada, and welcome to the site by the way!
 

seneca1

New member
Local time
Today, 08:40
Joined
Jun 18, 2018
Messages
8
I think I'll be poking around here more!

Time to start getting into the basics of VBA.
 

Users who are viewing this thread

Top Bottom