E-mail hyperlink with pre-defined message

[healey33]

Hey, I have a form with contact info (name, address, postal code, email etc.) and I have set up the e-mail as a hyperlink to outlook. I would like the hyperlink to not only start a new email to that person but also have text in the message field (a template). For example,
"Hi, [Name]

I would like to confirm your address of [Address] for delivery of a package.

Thanks"



Is something like this possible? The template would include much more text than this but if this works, I can do the template myself. If it is not possible to personalize the name and address automatically within the message, they can be entered manually in the message but ideally, I would like to do it automatically.

Let me know if any more clarification is needed. Thanks in advance for any help.

[pbaldy]

Shouldn't be difficult. One method would be to use SendObject to create the email, and you can build a string using values from a form or whatever to use in the body argument. More info on SendObject in VBA help. Building the string would look like:

strBody = "Hi " & Me.NameField & vbCrLf & VbCrLf & "I would like to confirm your address of " & Me.AddressField & " for delivery of a package."

[healey33]

Thanks! That does almost exactly what I would like but it does not accept more than 255 characters. Is there a way around the character limit?

[pbaldy]

I just did a brief test and it took 360 with no problem. What is your code? You may need to build the string over several lines, not all on one.

[healey33]

Ah yes. I had all of the string on the same line. It works perfectly, thanks!

[pbaldy]

Happy to help, and welcome to the site by the way!

[healey33]

Thank you! How about this one? I would like to select all of the e-mail addresses from the "Email" column in a table named "Signed Leases" and display them in a label without duplicates (eventually I will send them all to outlook for a mass e-mail but i am displaying them for trial purposes). I am currently receiving an error message "Compile Error : Expected Array" at the "emails(count) =" line and I cannot see what is wrong!

Private Sub Command3_Click()
Dim db As DAO.Database
Dim strWhere As String
Dim numberOfRecords As Integer
Dim count As Integer
Dim count2 As Integer
Dim count3 As Integer
Dim label As String
Dim emails As String
emails = Array()
Dim emailsNoDuplicates As String
emailsNoDuplicates = Array()
Dim duplicate As Boolean
Dim x As Integer
x = 1
label = ""




Set db = CurrentDb ' select current database
'numberOfRecords = DCount("[ID]", "Signed Leases") 'count number of records


For count = 0 To 50 'first loop through
    strWhere = "ID = " & count + 1
    If DLookup("[Email]", "Signed Leases", strWhere) <> "" Then 'if the cell is not empty, populate the first array
        emails(count) = DLookup("[Email]", "Signed Leases", strWhere)
        duplicate = False 'reset duplicate to false
        For count2 = 0 To count - 1 'second loop for checking only previous entries
            If emails(count) = emails(count2) Then 'if the email has already been looked at, trigger duplicate to true
            duplicate = True
            End If
        Next count2
        If duplicate <> True Then 'if it is not a duplicate, populate a new array with the email
            emailsNoDuplicates(x) = emails(count2)
            x = x + 1
        End If
    End If
Next count
Text4.SetFocus
For count3 = 0 To x - 1
    label = label & emailsNoDuplicates(count3) & "; "
    Text4.Text = label
Next count3


End Sub

Any help is greatly appreciated!

[healey33]

Dim x As Integer
x = 1

I realize this should be x = 0 but that is not the issue.

[pbaldy]

That would be really inefficient. Try this type of thing:

  Dim strSQL                  As String
  Dim db                      As DAO.Database
  Dim rs                      As DAO.Recordset
  Dim strAddys                As String

  Set db = CurrentDb()

  strSQL = "SELECT DISTINCT Email FROM [Signed Leases] WHERE Email Is Not Null"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF
    strAddys = strAddys & rs!Email & "; "
    rs.MoveNext
  Loop
  MsgBox strAddys

  Set rs = Nothing
  Set db = Nothing

[healey33]

Well that makes things a little easier! Thank you very much! How would I add other restrictions to this? for example I have a city field with about 12 different cities and would like to display the e-mail addresses within just one city (Toronto for example). Is it possible to add more parameters to this function?

Thanks again, I am still very new to Access and VBA.

[pbaldy]

strSQL is simply an SQL statement (a query) and could contain any number of parameters. You can even open the recordset on a saved query, though if it has form parameters you'll have an issue. You can create a query that does what you want and then use it directly or copy the SQL into the code.