Email addresses in a form

  • Thread starter Thread starter Brendan MAther
  • Start date Start date
B

Brendan MAther

I have a query that is presented in a form. I'd like to be able to click a
button on the form and it would automatically enter all of the email address
of all the contacts on that form into Outlook. Can anyone help me do this?
I would also like to be able to Fax to these contacts if they do not have an
email address, is this possible?

Thanks,

Brendan
 
I have this from a previous post written by Cheryl Fischer.
Good Luck

Jim

If I remember correctly, this code comes from a Microsoft
KB article.
You'll need, of course, to modify it to use your table,
form and field
names.


First, set a reference to the Microsoft Outlook xx.x Object
Library, where
xx.x is the version that you are using. Then, put the
following code behind
a command button on a form:

Dim oOutlook As New Outlook.Application
Dim colItems As Items
Dim rsCont As Recordset
Dim strSQL As String
Dim strMsg As String

strSQL = "Select ContactName, EmailAddr from
tblContacts " _
& "WHERE ContactName is not null and EmailAddr
is not null;"

Set rsCont = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

'Get a reference to the Items collection of the
contacts folder.
Set colItems = oOutlook.GetNamespace("MAPI"). _

GetDefaultFolder(olFolderContacts).Items

Do Until rsCont.EOF
If Not blnIsContact(rsCont!ContactName, colItems) Then
With colItems.Add
.FullName = rsCont!ContactName
.Email1Address = rsCont!emailaddr
.Save
End With
End If
rsCont.MoveNext
Loop
rsCont.Close

MsgBox "Done!"

End Sub
END OF CODE TO BE COPIED TO COMMAND BUTTON

PASTE THE FOLLOWING CODE INTO ONE OF YOUR PUBLIC MODULES
Public Function blnIsContact(strName As String, colItems As
Items) As
Boolean

Dim varItem As Variant
Dim strMsg As String

'Search for the FullName (strName) in Contacts. If it
is found,
'notify the user.
Set varItem = colItems.Find("[FullName] = """ & strName
& """")
If varItem Is Nothing Then
blnIsContact = False
Else
strMsg = "The contact named " & strName & " already
exists. " _
& Chr(13) & Chr(10) & "Do you want to add
this contact
anyway?"

If MsgBox(strMsg, vbYesNo) = vbYes Then
blnIsContact = False
Else
blnIsContact = True
End If
End If

End Function

END OF CODE TO BE COPIED TO PUBLIC MODULE.

hth,
 
Can't I just enter mailto: in the Hyperlink Address, then enter some code so
that it would automatically open outlook up with those addresses in the TO:
box? the code i have in there now is mailto:"Forms!me.email", but this just
brings "Forms!me.email" into the TO: box.

Thanks,
Brendan


Jim/Chris said:
I have this from a previous post written by Cheryl Fischer.
Good Luck

Jim

If I remember correctly, this code comes from a Microsoft
KB article.
You'll need, of course, to modify it to use your table,
form and field
names.


First, set a reference to the Microsoft Outlook xx.x Object
Library, where
xx.x is the version that you are using. Then, put the
following code behind
a command button on a form:

Dim oOutlook As New Outlook.Application
Dim colItems As Items
Dim rsCont As Recordset
Dim strSQL As String
Dim strMsg As String

strSQL = "Select ContactName, EmailAddr from
tblContacts " _
& "WHERE ContactName is not null and EmailAddr
is not null;"

Set rsCont = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

'Get a reference to the Items collection of the
contacts folder.
Set colItems = oOutlook.GetNamespace("MAPI"). _

GetDefaultFolder(olFolderContacts).Items

Do Until rsCont.EOF
If Not blnIsContact(rsCont!ContactName, colItems) Then
With colItems.Add
.FullName = rsCont!ContactName
.Email1Address = rsCont!emailaddr
.Save
End With
End If
rsCont.MoveNext
Loop
rsCont.Close

MsgBox "Done!"

End Sub
END OF CODE TO BE COPIED TO COMMAND BUTTON

PASTE THE FOLLOWING CODE INTO ONE OF YOUR PUBLIC MODULES
Public Function blnIsContact(strName As String, colItems As
Items) As
Boolean

Dim varItem As Variant
Dim strMsg As String

'Search for the FullName (strName) in Contacts. If it
is found,
'notify the user.
Set varItem = colItems.Find("[FullName] = """ & strName
& """")
If varItem Is Nothing Then
blnIsContact = False
Else
strMsg = "The contact named " & strName & " already
exists. " _
& Chr(13) & Chr(10) & "Do you want to add
this contact
anyway?"

If MsgBox(strMsg, vbYesNo) = vbYes Then
blnIsContact = False
Else
blnIsContact = True
End If
End If

End Function

END OF CODE TO BE COPIED TO PUBLIC MODULE.

hth,

--
Cheryl Fischer
.
Click to expand...
Click to expand...
 
Brendan,

Your table's hyperlink is not able to read a form control value, so this
approach will not work for you. The Hyperlink field must contain a valid
email address or internet link. IMO, these particular fields are more
trouble than they are worth because Access assumes that the field will be an
Internet link and, if an email address is needed, each record in the field
must be edited.

Is this post related to your posting in the Forms news group? If so, I just
posted some code on how to loop through a form's recordset to send your
emails.

hth,

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Brendan Mather said:
Can't I just enter mailto: in the Hyperlink Address, then enter some code so
that it would automatically open outlook up with those addresses in the TO:
box? the code i have in there now is mailto:"Forms!me.email", but this just
brings "Forms!me.email" into the TO: box.

Thanks,
Brendan


Jim/Chris said:
I have this from a previous post written by Cheryl Fischer.
Good Luck

Jim

If I remember correctly, this code comes from a Microsoft
KB article.
You'll need, of course, to modify it to use your table,
form and field
names.


First, set a reference to the Microsoft Outlook xx.x Object
Library, where
xx.x is the version that you are using. Then, put the
following code behind
a command button on a form:

Dim oOutlook As New Outlook.Application
Dim colItems As Items
Dim rsCont As Recordset
Dim strSQL As String
Dim strMsg As String

strSQL = "Select ContactName, EmailAddr from
tblContacts " _
& "WHERE ContactName is not null and EmailAddr
is not null;"

Set rsCont = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

'Get a reference to the Items collection of the
contacts folder.
Set colItems = oOutlook.GetNamespace("MAPI"). _

GetDefaultFolder(olFolderContacts).Items

Do Until rsCont.EOF
If Not blnIsContact(rsCont!ContactName, colItems) Then
With colItems.Add
.FullName = rsCont!ContactName
.Email1Address = rsCont!emailaddr
.Save
End With
End If
rsCont.MoveNext
Loop
rsCont.Close

MsgBox "Done!"

End Sub
END OF CODE TO BE COPIED TO COMMAND BUTTON

PASTE THE FOLLOWING CODE INTO ONE OF YOUR PUBLIC MODULES
Public Function blnIsContact(strName As String, colItems As
Items) As
Boolean

Dim varItem As Variant
Dim strMsg As String

'Search for the FullName (strName) in Contacts. If it
is found,
'notify the user.
Set varItem = colItems.Find("[FullName] = """ & strName
& """")
If varItem Is Nothing Then
blnIsContact = False
Else
strMsg = "The contact named " & strName & " already
exists. " _
& Chr(13) & Chr(10) & "Do you want to add
this contact
anyway?"

If MsgBox(strMsg, vbYesNo) = vbYes Then
blnIsContact = False
Else
blnIsContact = True
End If
End If

End Function

END OF CODE TO BE COPIED TO PUBLIC MODULE.

hth,

--
Cheryl Fischer
.
Click to expand...
Click to expand...
Click to expand...
 
Thank you


Cheryl Fischer said:
Brendan,

Your table's hyperlink is not able to read a form control value, so this
approach will not work for you. The Hyperlink field must contain a valid
email address or internet link. IMO, these particular fields are more
trouble than they are worth because Access assumes that the field will be an
Internet link and, if an email address is needed, each record in the field
must be edited.

Is this post related to your posting in the Forms news group? If so, I just
posted some code on how to loop through a form's recordset to send your
emails.

hth,

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Brendan Mather said:
Can't I just enter mailto: in the Hyperlink Address, then enter some
Click to expand...
code
so
that it would automatically open outlook up with those addresses in the TO:
box? the code i have in there now is mailto:"Forms!me.email", but this just
brings "Forms!me.email" into the TO: box.

Thanks,
Brendan


Jim/Chris said:
I have this from a previous post written by Cheryl Fischer.
Good Luck

Jim

If I remember correctly, this code comes from a Microsoft
KB article.
You'll need, of course, to modify it to use your table,
form and field
names.


First, set a reference to the Microsoft Outlook xx.x Object
Library, where
xx.x is the version that you are using. Then, put the
following code behind
a command button on a form:

Dim oOutlook As New Outlook.Application
Dim colItems As Items
Dim rsCont As Recordset
Dim strSQL As String
Dim strMsg As String

strSQL = "Select ContactName, EmailAddr from
tblContacts " _
& "WHERE ContactName is not null and EmailAddr
is not null;"

Set rsCont = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

'Get a reference to the Items collection of the
contacts folder.
Set colItems = oOutlook.GetNamespace("MAPI"). _

GetDefaultFolder(olFolderContacts).Items

Do Until rsCont.EOF
If Not blnIsContact(rsCont!ContactName, colItems) Then
With colItems.Add
.FullName = rsCont!ContactName
.Email1Address = rsCont!emailaddr
.Save
End With
End If
rsCont.MoveNext
Loop
rsCont.Close

MsgBox "Done!"

End Sub
END OF CODE TO BE COPIED TO COMMAND BUTTON

PASTE THE FOLLOWING CODE INTO ONE OF YOUR PUBLIC MODULES
Public Function blnIsContact(strName As String, colItems As
Items) As
Boolean

Dim varItem As Variant
Dim strMsg As String

'Search for the FullName (strName) in Contacts. If it
is found,
'notify the user.
Set varItem = colItems.Find("[FullName] = """ & strName
& """")
If varItem Is Nothing Then
blnIsContact = False
Else
strMsg = "The contact named " & strName & " already
exists. " _
& Chr(13) & Chr(10) & "Do you want to add
this contact
anyway?"

If MsgBox(strMsg, vbYesNo) = vbYes Then
blnIsContact = False
Else
blnIsContact = True
End If
End If

End Function

END OF CODE TO BE COPIED TO PUBLIC MODULE.

hth,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top