Collect email addresses from a query to

I want to email to a group of people. How can I collect the selected records 
from a query's [EmailAddress] field, put commas between each address, and 
drop the result into a microsoft outlook TO field?

TIA,
Kathy 


0
Kathy
8/30/2007 6:56:25 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
546 Views

Similar Articles

[PageSpeed] 59

"Kathy Webster" wrote:
>I want to email to a group of people. How can I collect the selected 
>records from a query's [EmailAddress] field, put commas between each 
>address, and drop the result into a microsoft outlook TO field?
>
Hi Kathy,

Here be some "starting skeleton code"
(usually one wants to put the "list"
 in the BCC)

'***** start of code ******************
Public Function SendMsg()
On Error GoTo Err_SendMsg
   Dim appOutlook As New Outlook.Application
   Dim itm As Outlook.MailItem

   'Create new mail msg
   Set itm = appOutlook.CreateItem(olMailItem)

   With itm
      '.To = "<youremailadressifyouwish>"
      .BCC = GetEMailAddresses("qryEMail", "EmailAddress")
      '.Subject = "A subject if you wish"
      '.Body = "Something in the body if you wish."
      'display msg before hitting Send
      .Display
   End With

Exit_SendMsg:
    Exit Function
Err_SendMsg:
    MsgBox Err.Description
    Resume Exit_SendMsg

End Function

Public Function GetEMailAddresses(pQueryName As String, _
                           pFieldName As String) As String
On Error GoTo Err_GetEMailAddress
    Dim rs As DAO.Recordset
    Dim varTemp As Variant

    varTemp = ""

    Set rs = CurrentDb.OpenRecordset(pQueryName)
    rs.MoveFirst
    Do While Not rs.EOF
        varTemp = varTemp & "<" & rs.Fields(pFieldName) & ">, "
        rs.MoveNext
    Loop
    'remove ending comma and space
    GetEMailAddresses = Left(varTemp, Len(varTemp) - 2)
    'Debug.Print varTemp
    rs.Close

Exit_GetEMailAddress:
    Set rs = Nothing
    Exit Function

Err_GetEMailAddress:
    MsgBox Err.Description
    Resume Exit_GetEMailAddress

End Function
..*** end of code ***************

There are several things *wrong* with above code...

It uses early binding, plus, it won't work if there
are several users on the same machine.

Ignoring binding for now, you will have to get
a namespace if multiple users (untested code):

Public Function SendMsg ()
On Error GoTo Err_SendMsg

    Dim appOutlook As Outlook.Application
    Dim NS As Outlook.NameSpace
    Dim itm As Outlook.MailItem

    Set appOutlook = CreateObject("Outlook.Application")
    '********************************
    Set NS = appOutlook.GetNamespace("MAPI")
    NS.GetDefaultFolder olFolderOutbox
    '**********************************
    Set itm = appOutlook.CreateItem(olMailItem)


      'itm.To = "<youremailadressifyouwish>"
      itm.BCC = GetEMailAddresses("qryEMail", "EmailAddress")
      'itm.Subject = "A subject if you wish"
      'itm.Body = "Something in the body if you wish."
      'display msg before hitting Send
      itm.Display

Exit_SendMsg:
    Set itm = Nothing
    Set NS = Nothing
    Set appOutlook = Nothing

    Exit Function
Err_SendMsg:
    MsgBox Err.Description
    Resume Exit_SendMsg
End Function

With this early binding, that means that every
computer you place this app on must have same
version of Outlook that you use in References
when you compile this code.

If that meets your situation, then I believe you
are mostly there (except maybe you want to add
some function parameters to SendMsg for main
"To" address, a "Subject" string, and a "Body"
string that you can feed to your function).

(again, untested code, w/o code to check
  for valid parameter strings):

Public Function SendMsg (pTo As Variant, _
                                         pSubject As Variant, _
                                         pBody As Variant, _
                                        pQueryName As String, _
                                        pFieldName As String) As Boolean
On Error GoTo Err_SendMsg

    Dim appOutlook As Outlook.Application
    Dim NS As Outlook.NameSpace
    Dim itm As Outlook.MailItem

    Set appOutlook = CreateObject("Outlook.Application")
    '********************************
    Set NS = appOutlook.GetNamespace("MAPI")
    NS.GetDefaultFolder olFolderOutbox
    '**********************************
    Set itm = appOutlook.CreateItem(olMailItem)


      itm.To = "<" & pTo & ">"
      itm.BCC = GetEMailAddresses(pQueryName, pFieldName)
      itm.Subject = pSubject & ""
      itm.Body = pBody & ""
      'display msg before hitting Send
      itm.Display

      'return that successful
      SendMsg = True

Exit_SendMsg:
    Set itm = Nothing
    Set NS = Nothing
    Set appOutlook = Nothing

    Exit Function
Err_SendMsg:
    MsgBox Err.Description
    Resume Exit_SendMsg
End Function

So...save the 2 functions in a new code module
(say "modEMail"),
click on Debug/Compile to make sure copy/paste
didn't mangle with wrapping,

then in Immediate Window, try testing
(hit <ENTER> after typing each line)

strQ = "nameofyourquery"
strF = "nameof fieldinquery"
strTo = someaddress@someip.net
strS = "this is the subject line"
strB = "this is body of email"
SendMsg strTo, strS, strB, strQ, strF

good luck,

gary






0
Gary
8/31/2007 12:15:43 PM
> then in Immediate Window, try testing
> (hit <ENTER> after typing each line)
>
> strQ = "nameofyourquery"
> strF = "nameof fieldinquery"
> strTo = someaddress@someip.net
> strS = "this is the subject line"
> strB = "this is body of email"
> SendMsg strTo, strS, strB, strQ, strF
>

Hi Kathy,

First...I notice strTo gets changed to
a hyperlink in newsgroup, so make sure
it has quotes around it.

Second...when we assign a string value
to a variable in Immediate Window, it
actually will be a *Variant* -- not a string.

So...if you try to execute the last line above,
you will get a "ByVal argument mismatch"
error, i.e, strQ and strF need to be converted
to strings to match up with function. So, last line
should be:

SendMsg strTo, strS, strB, CStr(strQ), Cstr(strF)

Finally, I forgot to give you a late-binding version
(so you do not need to have Outlook tagged in
References...but you do need to reference DAO)

Note: because we use late binding, the "ol" variables
had to be replaced with their actual values.

The below was tested in Access2002:

Public Function SendMsgLateBinding(pTo As Variant, _
                        pSubject As Variant, _
                        pBody As Variant, _
                        pQueryName As String, _
                        pFieldName As String) As Boolean
On Error GoTo Err_SendMsgLateBinding

    Dim appOutlook As Object
    Dim NS As Object
    Dim itm As Object

    Set appOutlook = CreateObject("Outlook.Application")
    '********************************
    Set NS = appOutlook.GetNamespace("MAPI")
    'olFolderOutbox = 4
    NS.GetDefaultFolder 4
    '**********************************
    'olMailItem = 0
    Set itm = appOutlook.CreateItem(0)


      itm.To = "<" & pTo & ">"
      itm.BCC = GetEMailAddresses(pQueryName, pFieldName)
      itm.Subject = pSubject & ""
      itm.Body = pBody & ""
      'display msg before hitting Send
      itm.Display

      'return that successful
      SendMsgLateBinding = True

Exit_SendMsgLateBinding:
    Set itm = Nothing
    Set NS = Nothing
    Set appOutlook = Nothing

    Exit Function
Err_SendMsgLateBinding:
    MsgBox Err.Description
    Resume Exit_SendMsgLateBinding
End Function

good luck,

gary 


0
Gary
8/31/2007 1:16:37 PM
Thank you, Gary. I'm breaking into a cold sweat since this is over my head, 
but I am going to attempt it.  2 questions:
1. Where do I insert the query name in this code?  My query name is qEmails.
2. Where do I insert the email field name into this code? My email field 
name is em_add
Kathy

"Gary Walter" <gary@wrotein.msg> wrote in message 
news:OYIvnE96HHA.5984@TK2MSFTNGP04.phx.gbl...

> The below was tested in Access2002:
>
> Public Function SendMsgLateBinding(pTo As Variant, _
>                        pSubject As Variant, _
>                        pBody As Variant, _
>                        pQueryName As String, _
>                        pFieldName As String) As Boolean
> On Error GoTo Err_SendMsgLateBinding
>
>    Dim appOutlook As Object
>    Dim NS As Object
>    Dim itm As Object
>
>    Set appOutlook = CreateObject("Outlook.Application")
>    '********************************
>    Set NS = appOutlook.GetNamespace("MAPI")
>    'olFolderOutbox = 4
>    NS.GetDefaultFolder 4
>    '**********************************
>    'olMailItem = 0
>    Set itm = appOutlook.CreateItem(0)
>
>
>      itm.To = "<" & pTo & ">"
>      itm.BCC = GetEMailAddresses(pQueryName, pFieldName)
>      itm.Subject = pSubject & ""
>      itm.Body = pBody & ""
>      'display msg before hitting Send
>      itm.Display
>
>      'return that successful
>      SendMsgLateBinding = True
>
> Exit_SendMsgLateBinding:
>    Set itm = Nothing
>    Set NS = Nothing
>    Set appOutlook = Nothing
>
>    Exit Function
> Err_SendMsgLateBinding:
>    MsgBox Err.Description
>    Resume Exit_SendMsgLateBinding
> End Function
>
> good luck,
>
> gary
> 


0
Kathy
9/11/2007 10:14:57 PM
Reply:

Similar Artilces:

can't download pop3 email
This is my error message Task Receiving reported error 0x800CCC0F The connection to the server was interrupted If this problem continues contact your server administrator or your ISP The server responded +OK How do I fix this? What did your ISP say? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After scratching one's head, M Pond <anonymous@discussions.microsoft.com> asked this group: | This is my error mes...

send from address
Hi all, is there any way to automatically change the from email address for all messages that go through a certain smtp connector? I want all messages going through that connector to look like they were sent from one specific mailbox / person. We need to make sure all replies end up in the same place. Thanks Rogier. Not in exchange natively. Typical solutions to this are implementing a "smart host" to re-write the headers, and routing all outbound through that host. Sendmail can do this, and there are other products out there for the Windows platform that can do this, ie...

Off Line Address Book
After upgrading Exchange 5.5 to Exchange 2003, my remote users are not able to Download the Off Line Address Book. The error that is received by the remote user is [Task "Microsoft Exchange Server" Reported Error (0x8004010F): 'The Operation Failed. An Object Could not be Found.'] Thank you for your support. Debi I have the same problem but have not yet taken care of it. ...made som research and found that others with the same problem have tried to reset site folders. http://support.microsoft.com/default.aspx?scid=kb;en-us;273364 http://support.microsoft.com/default...

Printing an address in letter
I am trying to print a confirmation letter with an address in the body of the report. The address may or may not have an Address2 line. How can I eliminate the space if the Address2 line is blank? Also, can I use an "IF" to edit the printing of zero values? Thanks, tomc Depends on what you mean about the address being in the body of the report. If there is no label with the address or any text around it then use Properties to set click on the text box (es) in Design view and set Can Grow and Can Shrink to yes. Do the same for the Section that contains the address by clicking ...

Formula In A Query
Hi I have a query in which I am trying to create a formula to check if the date in a record is in the same month as the current date, if this is true to return the months name, if not to return another value. The formula does not fall over but only returns the OR value. The OR value is meaningless at the moment as I am just seeing if the formula is actually doing something Could someone have a look at this MonthNo: IIf([Date SubmittedtoLab]=Month(Date()),MonthName(Month([Date SubmittedtoLab])),5) Thanks Richard Hi Richard, You can use the Immediate Window (open with <Ctrl><...

Anyway To Always bcc same email address automatically
Is there anyway to set up a rule or something so that everytime I send an email it is bcc'd to a specific email address? Thanks. Rick Bellefond RB Data Services rick@rbdata.com Do not know if this works in 2003 but here is a link to some code that will do this for 2000: http://www.slipstick.com/dev/code/autobcc.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Rick Bellefond asked: ...

excel pivot table with sql query and visual basic macros
I have a pivot table that runs off a sql query, it has macros and parameters built into the report. It worked until the client updated their server and db software. When debugging this, on the module under the closedb it stops here: Sub CloseDB() 'IF CONNECTION IS OPEN IT NEEDS TO BE CLOSED SO AN ERROR IS NOT THROWN If rsData.State = 1 Then rsData.Close Set rsData = Nothing End If cnData.Close Set cnData = Nothing Set Cmd = Nothing End Sub Thanks ...

Some users not listed in Global Address Book ???
Dear Sir, I have some of my users in my exchange 2003, their contacts are not listed in the GAL although I can send them e-mails, and they can receive. What Should I have to do to make their contacts appear in the GAL? On Thu, 13 Jul 2006 00:12:01 -0700, Mahmoud Metwally Ali <Mahmoud Metwally Ali@discussions.microsoft.com> wrote: >Dear Sir, I have some of my users in my exchange 2003, their contacts are not >listed in the GAL although I can send them e-mails, and they can receive. >What Should I have to do to make their contacts appear in the GAL? Are you talking about user...

Duplicated Email
Exchange 2003; when an email is sent to the Exchange domain from a remote email address and more than 1 address is placed in the to: or cc: field, then when the email is received everyone receives 1 copy per addresse, in other words exchange is reading the resend as well as the to: and cc Is this a bug within exchange 2003, is there a way of configuring exchange to not look at the resend in the header. Using some kind of POP3 "connector"? Richard Schofield wrote: > Exchange 2003; when an email is sent to the Exchange domain from a remote email address and more than 1 addres...

Need to print all address lines on a 1099 form
In Great Plains Version 10, on the vendor card, under the address button, you are able to enter 3 lines of address information. However, when printing 1099's, only the first 2 lines of address information will print on the form. Since we utilize all 3 address lines, we had many 1099 forms returned as undeliverable this past year. According to our partner, Microsoft indicated that, currently, there is no way to print the third line of information. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for ...

Missing Email Attachments
Hi all, please help! We have been sent emails from a partner company which are missing attachments. Upon investigation they have been sending emails composed in rich text format. KB 138053 provided this... "When an end user sends mail to the Internet from an Exchange Windows or Outlook client, a file attachment called Winmail.dat may be automatically added to the end of the message if the recipient's client cannot receive messages in Rich Text Format (RTF). The Winmail.dat file contains Exchange Server RTF information for the message, and may appear to the recipient as a bina...

Emailing Question from my DB
Presently I have a this code to email my Report when I close it, Is there any way that I can code my Batch Report to email remembering each report may have a different email address?? Below Is my Print Batch Invoice & My emailing code for single Report ------------------------------------------- Case "PrintInvoiceBatch" If IsNull(tbDateFrom.value) Or tbDateFrom.value = "" Or IsNull(tbDateTo.value) Or tbDateTo.value = "" Then MsgBox "Please Enter the Begining Date and End Date.", vbApplicationModal + vbInform...

Workflow generated email for reassignment of Opportunity
We have a CSR that inputs Opportunities in to CRM and then assigns them to the appropriate Salesperson based on Account Ownership. I tried creating a workflow process that automatically sends an email to the Account Owner that an Opportunity has been assigned to them. Unfortunately the recipient box is grayed out and by default the email is sent to the Account. Is there a workflow process in place that can handle my intentions? Actually, you can send email to designated recipients with Workfow, you just can't use a template. For some reason, it was setup this way. You could code a ...

"To: contains a phrase" filter does not work for outgoing emails
I have created filter specifying a phrase from the TO: field of an email. Such emails should be placed in a folder. This does not work for emails sent from my outlook. How to place an outgoing email in a specific folder based on the TO: field? O. Ondrej Sevecek <ondra at my_surname dot com> wrote: > I have created filter specifying a phrase from the TO: field of an > email. Such emails should be placed in a folder. This does not work > for emails sent from my outlook. > How to place an outgoing email in a specific folder based on the TO: > field? Use the "wit...

How can I pre-filter a table that is used in a query in a live dat
How can I pre-filter a table that is used in a query in a live database? I have about 47 queries (SQL text in a memo field in a table) that all reference a few tables in the same database. I would like to limit the table’s data instead of re-writing all the queries to include further criteria. The queries are very complex and it is not desirable to re-write the queries and use parameters. This database is live on an ASP.NET web page and there may be multiple people viewing the reports which are using these queries. Since there are multiple users I can not simply copy the tables into ...

Unable to send emails #2
My mail server is not sending out emails. When I perform an NSLookup on the server's IP address I get an UNKNOWN for the server name. Any ideas would be appreciated. Thanks "TPSchaefer" <TPSchaefer@hotmail.com> wrote: >My mail server is not sending out emails. When I perform an NSLookup on the >server's IP address I get an UNKNOWN for the server name. > >Any ideas would be appreciated. Fix your DNS or your network connection to your DNS. -- Rich Matheisen MCSE+I, Exchange MVP MS Exchange FAQ at http://www.swinc.com/resource/exch_faq.htm I have...

Creating a query out of another query
Hi Guys, I have a query looking like that: field1 field2 field3 field4 field5 field6 Data 1 Test 6 Poly 8 Data 5 Poly 10 I need a query from this query like this: Field 1 Field 2 Data 1 Test 6 Poly 8 Data 5 Poly 10 I'll really appreciate if someone can help me with this one Thanks This should do it -- SELECT Field1 AS Field_1, Field2 AS Field_2 FROM YourTable WHERE Field1 ...

Scheduling Outgoing Emails?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop I've seen this question asked before but it hasn't been answered. Is there a way to schedule outgoing messages in Entourage so that you can write a series of emails, schedule them, and have them run on their own? <br><br>Thanks. On 5/16/10 10:41 AM, in article 59bb88f7.-1@webcrossing.JaKIaxP2ac0, "johanna@officeformac.com" <johanna@officeformac.com> wrote: > Is there a way to schedule outgoing messages in Entourage so that you can > write a se...

emails sent from work
I sent emails from work to home address yet the messages are not in my inbox. Why not? do I have to set up somehow? "dummy" <dummy@discussions.microsoft.com> wrote in message news:32256DDD-4E7B-48F7-AF7A-2D2080B94BFB@microsoft.com > I sent emails from work to home address yet the messages are not in > my inbox. Why not? do I have to set up somehow? Inbox where? ...

Urgent Send email problem!!!
OK Techies; this is a baffler! Neither Netscape-7.1 nor Outlook-2000 send emails from my desktop. I can receive from any email client but can't send from the client. I can send form my ISP's Webserver, but not from my desktop client. I'm using Verizon DSL. Everything else (HW / SW) on the PC works fine; P-3 1 GHz. w/512 Mb. RAM - 80 Gb HD; running Win2k-Pro SP4. It seems as though port 25 (SMTP) is blocked somehow. I tried another DSL modem; no change.It's obviously not the modem, or is it the email client-app (I've used several). I've tried sending with and...

Use Excel to update Outlook address book
hi all... Is there a way to update outlook address book using excel? Is there a macro available for it? Thank you for your answers Mike ...

Convert activity (email, letter, etc) to case bug?
When converting any activity to a case in CRM 4.0, using the default Convert To Case button, I noticed the case's picklist caseorigincode not being set properly. This picklist holds values matching the activity types, such as email and letter. Is there a way to set this using relationships, workflow or whatever? I did notice that my picklist values are all above 200.000, and I cannot set them to something like 1,2,3. Perhaps these old values are registrered for such a mapping. Unfortunately, they were deleted some time ago and reset again. Anyone? -- CRM consultant ...

Tracking an email when sent directly from Excel
I often use the feature File>Send To>Mail Reciepient (as attachment) when I'm working in an Excel Workbook. However I can never find any record of this in Outlook, or anywhere else. Is there a way so this gets recorded in Outlook, or can you tell me where I can find some record of the email and its contents. Thank you, On May 18, 7:23=A0pm, Rob <robfl...@sbcglobal.net> wrote: > I often use the feature File>Send To>Mail Reciepient (as attachment) > when I'm working in an Excel Workbook. =A0However I can never find any > record of this in Outloo...

Can I Invite a Public Calendar on an Email?
I would like to be able to add an appointment to my personal calendar and invite the public calendar too to get that appointment into the public calendar. I have read that this is possible because all public folders have an email address, but I have not been able to figure out how to do this. Thanks! right click on the pf calendar and choose properties - there is an option to add the calendar to your address book. you can also guess at the address - many times the folder name is the email alias - if its longer than 8 characters, try the first 8. -- Diane Poremsky [MVP - Outlook] Autho...

query help 01-08-08
My main table is called tblMain and this has an abundance of information in there. Each record has an ID called MainID. I need to do a 'search type query' on 4 fields . I have a primary business unit which each record must fill in (PrimaryBU). I then have another 3 related business unit fields (RelatedBU1, RelatedBU2, RelatedBU3). All 4 of these fields are linked to a lookup table tblBusinessUnit. I need to write a query that will prompt the user to type a business unit (I know how to do this part) and will then show any record which has that business unit in any of those 4 fie...