emailing from a form

[Brendan MAther]

I have a table called Contact_Info. I have a form that allows me to show
all the contacts from a specified city and sector. Once these contacts
appear on my new form I would like to be able to press a button and it would
take their email addresses, from the 'email' field, and enter them into
Outlook in the "TO:" box so that they can all be emailed the same document.
Any help would be much appreciated, thank you,
Brendan

--
Disclaimer: Any comments made are personal and do not reflect the thoughts
or policies of this company.

[Jerry Boone]

First you will need to add a reference to Microsoft Outlook in your VBA
editor, then use this code in an event.

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient

Set objOutlook = New Outlook.Application
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
.Recipients.Add ("john doe")
.BodyFormat = 1 '1 for text, 2 for html
.Subject = "My Subject"
If iFormat = 2 Then
.HTMLBody = Message
Else
.Body = "My Message" 'can use vbCrLf if you want line breaks
above or below
End If
SendEmail = True
.Display 'if you want to show it, doe this, otherwise leave it off
End With
Set objOutlook = Nothing

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
"Brendan MAther" <br************@gems4.gov.bc.ca> wrote in message
news:3f********@obsidian.gov.bc.ca...

I have a table called Contact_Info. I have a form that allows me to show
all the contacts from a specified city and sector. Once these contacts
appear on my new form I would like to be able to press a button and it would take their email addresses, from the 'email' field, and enter them into
Outlook in the "TO:" box so that they can all be emailed the same document. Any help would be much appreciated, thank you,
Brendan

--
Disclaimer: Any comments made are personal and do not reflect the thoughts
or policies of this company.

[Tony Johnson]

Hello,

(I am not sure if this is going to Jerry or not but he replied to the
previous message)
I have a similar problem. However, I have a report that I want to email
to vendors. It is an outstanding report so therefore each report is
different. On the report I have Co. info such as ph, fax email. The
report depepends on how many vendors are past due but is usually about
50 separate pages. Is there a way to send the report to their owners
and each one as a totally separate email? I use Lotus Notes.

Thank you,
TJ

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Brendan MAther]

Thanks jerry, but how can I make it so that it will grab all the email
addresses in that form and put them all in the same email in the TO: box?
I tried .Recipients.Add (Forms!ListCommCity!email), i also tried
ListCommCity.email and ListCommCity.[email]. It will only list whichever
contact is selected, by default that is the first contact in the form.

thanks,

Brendan

"Jerry Boone" <je***@antech.biz.killspam> wrote in message
news:uD*****************@newssvr23.news.prodigy.co m...

First you will need to add a reference to Microsoft Outlook in your VBA
editor, then use this code in an event.

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient

Set objOutlook = New Outlook.Application
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
.Recipients.Add ("john doe")
.BodyFormat = 1 '1 for text, 2 for html
.Subject = "My Subject"
If iFormat = 2 Then
.HTMLBody = Message
Else
.Body = "My Message" 'can use vbCrLf if you want line breaks
above or below
End If
SendEmail = True
.Display 'if you want to show it, doe this, otherwise leave it off
End With
Set objOutlook = Nothing

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
"Brendan MAther" <br************@gems4.gov.bc.ca> wrote in message
news:3f********@obsidian.gov.bc.ca...

I have a table called Contact_Info. I have a form that allows me to show all the contacts from a specified city and sector. Once these contacts
appear on my new form I would like to be able to press a button and it

would

take their email addresses, from the 'email' field, and enter them into
Outlook in the "TO:" box so that they can all be emailed the same

document.

Any help would be much appreciated, thank you,
Brendan

--
Disclaimer: Any comments made are personal and do not reflect the thoughts or policies of this company.

[Jerry Boone]

You will have to loop through the listbox and call the add method each time.

So... something like this would need to be incorporated, this example works
on just the items you would select in the list... (if you want all of them
every time, then you will need to use different properties to iterate
through the list, you will have to do the looking on that one though).

.....
For Each Item In Me.myList.ItemsSelected
.Recipients.Add (Me.myList.ItemData(Item))
Next
......

Let me know how it turns out... ok?

:)

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access

"Brendan MAther" <br************@gems4.gov.bc.ca> wrote in message
news:3f********@obsidian.gov.bc.ca...

Thanks jerry, but how can I make it so that it will grab all the email
addresses in that form and put them all in the same email in the TO: box?
I tried .Recipients.Add (Forms!ListCommCity!email), i also tried
ListCommCity.email and ListCommCity.[email]. It will only list whichever
contact is selected, by default that is the first contact in the form.

thanks,

Brendan

"Jerry Boone" <je***@antech.biz.killspam> wrote in message
news:uD*****************@newssvr23.news.prodigy.co m...

First you will need to add a reference to Microsoft Outlook in your VBA
editor, then use this code in an event.

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient

Set objOutlook = New Outlook.Application
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
.Recipients.Add ("john doe")
.BodyFormat = 1 '1 for text, 2 for html
.Subject = "My Subject"
If iFormat = 2 Then
.HTMLBody = Message
Else
.Body = "My Message" 'can use vbCrLf if you want line breaks
above or below
End If
SendEmail = True
.Display 'if you want to show it, doe this, otherwise leave it off

End With
Set objOutlook = Nothing

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
"Brendan MAther" <br************@gems4.gov.bc.ca> wrote in message
news:3f********@obsidian.gov.bc.ca...

I have a table called Contact_Info. I have a form that allows me to

show all the contacts from a specified city and sector. Once these contacts appear on my new form I would like to be able to press a button and it

would

take their email addresses, from the 'email' field, and enter them into Outlook in the "TO:" box so that they can all be emailed the same

document.

Any help would be much appreciated, thank you,
Brendan

--
Disclaimer: Any comments made are personal and do not reflect the thoughts or policies of this company.

[Jerry Boone]

Possibly... that is if access can produce a Notes email using menu options
like Send To... otherwise, sorry - get Outlook running! :)

This will output a report object to an email...

DoCmd.SendObject acSendReport, "myReport", acFormatSNP,
"so*****@somewhere.com", , , , , True

You should then run this code for each separate email address.
--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access


"Tony Johnson" <et*@wt.net> wrote in message
news:3f*********************@news.frii.net...

Hello,

(I am not sure if this is going to Jerry or not but he replied to the
previous message)
I have a similar problem. However, I have a report that I want to email
to vendors. It is an outstanding report so therefore each report is
different. On the report I have Co. info such as ph, fax email. The
report depepends on how many vendors are past due but is usually about
50 separate pages. Is there a way to send the report to their owners
and each one as a totally separate email? I use Lotus Notes.

Thank you,
TJ

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Tony Toews]

Tony Johnson <et*@wt.net> wrote:

Is there a way to send the report to their owners
and each one as a totally separate email?
See the Recordset Looping Logic page at the top of the Access Email FAQ at my
website.
I use Lotus Notes.

See the Lotus Notes specific links on the same page mentioned above.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Pieter Linden]

"Brendan MAther" <br************@gems4.gov.bc.ca> wrote in message news:<3f********@obsidian.gov.bc.ca>...

Thanks jerry, but how can I make it so that it will grab all the email
addresses in that form and put them all in the same email in the TO: box?
I tried .Recipients.Add (Forms!ListCommCity!email), i also tried
ListCommCity.email and ListCommCity.[email]. It will only list whichever
contact is selected, by default that is the first contact in the form.

I'm assuming you have the Multi-Select property of your listbox set to
Extended... You have to iterate through the ItemsSelected collection
of the listbox and then do something with them...

from: http://www.mvps.org/access/forms/frm0007.htm

Q) I have a MultiSelect listbox control on my form. I want to pass the
selected items to a query as a parameter. How do I do this?

(A) Unlike simple listbox controls which can be referenced as a
parameter by a query, MultiSelect listboxes cannot be used directly as
a parameter. This is because calling the listbox
(Forms!frmMyForm!lbMultiSelListBox) from anywhere will not
automatically concatenate all the selected items. You need to build
the criteria yourself.

Note: You can still use a parameterized query provided you pass the
entire Where clause to it via code as a parameter. (eg. Have the query
reference a hidden control to which you manually assign the complete
WHERE clause using the following logic.)

For example,

'******************** Code Start ************************
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID]="
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [EmpID]="
Next varItem

'Trim the end of strSQL
strSQL=left$(strSQL,len(strSQL)-12))
'******************** Code end ************************
Okay, so in your case...

dim ctl as control
set ctl = me!lbAddresses
For Each varItem in ctl.ItemsSelected
olkMsg.Recipients.Add ctl.ItemData(varItem)
Next varItem
'create the rest of the message...
'(you know how to do that, I presume)
'then send
olkMsg.Send

[Pieter Linden]

Tony Johnson <et*@wt.net> wrote in message news:<3f*********************@news.frii.net>...

Hello,

(I am not sure if this is going to Jerry or not but he replied to the
previous message)
I have a similar problem. However, I have a report that I want to email
to vendors. It is an outstanding report so therefore each report is
different. On the report I have Co. info such as ph, fax email. The
report depepends on how many vendors are past due but is usually about
50 separate pages. Is there a way to send the report to their owners
and each one as a totally separate email? I use Lotus Notes.

Thank you,
TJ

Have a look at the E-mail section of Tony Toews' site...
http://www.granite.ab.ca/access/emaillotusnotes.htm

I *think* you may have to output the report to RTF or something and
then attach it. So *maybe* you have to base your report on a dummy
query that you modify on the fly, because at least in 2000, if you
sent a filtered report, the filter somehow got ignored and the whole
report got output...

[Tony Toews]

pi********@hotmail.com (Pieter Linden) wrote:

I *think* you may have to output the report to RTF or something and
then attach it. So *maybe* you have to base your report on a dummy
query that you modify on the fly, because at least in 2000, if you
sent a filtered report, the filter somehow got ignored and the whole
report got output...

Or as per "Emailing reports as attachments from Microsoft Access"

However you can not pass a filter or where clause as you would in a standard
OpenReport command in VBA. Therefore you must change the filter or where clause
yourself in the reports OnOpen event.

Me.Filter = "ShipmentsID=" & Forms![Select Load List]![LoadID]
Me.FilterOn = True

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm