Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

RE: Emailing from Microsoft Access

Discussion in 'Information Technology' started by Suzy, Nov 18, 2005.

  1. Suzy

    Suzy
    Expand Collapse
    Guest

    Hi there...

    Could you please explain a little more in detail for this poor newbie?
    Specifically, how do I get all of my client's email addresses from the table
    "ClientInfo" in the field "Email Address" into the BCC field?

    I have made a button and am trying to complete the macro to send an email to
    all my clients. If I put nothing in the To, CC and BCC fields I get a new
    email message ready for addressing. No worries there! However I would like
    all the client's email addresses to be automatically entered into the BCC
    field and I can't figure how to make the "BCC:=strBCC" that you wrote below
    work...

    Instructions for Dummies please... I'm a new user!

    Thanks,
    Suzy

    "Tom Wickerath" wrote:

    > Hi Simon,
    >
    > Instead of using the shell command, perhaps you should look into using
    > DoCmd.SendObject instead. The ObjectType and ObjectName arguments are
    > optional, if you don't want to include an attached object.
    >
    > Here is an example for sending the results of a query as an attached Excel
    > spreadsheet:
    >
    > DoCmd.SendObject acQuery, "qryManufacturers", _
    > OutputFormat:=acFormatXLS, _
    > To:=strTo, CC:=strCC, BCC:=strBCC, _
    > Subject:=strSubject, EditMessage:=True
    >
    > and here is a simplier example, if you don't need to include an attached
    > object:
    >
    > DoCmd.SendObject _
    > To:=strTo, CC:=strCC, BCC:=strBCC, _
    > Subject:=strSubject, EditMessage:=True
    >
    >
    > Tom
    >
    > http://www.access.qbuilt.com/html/expert_contributors.html
    > __________________________________________
    >
    > "simondhopkin@yahoo.co.uk" wrote:
    >
    > Wondering how to trigger Outlook Express from within Access with
    > preset BCC: addresses
    >
    > Have a database containing a list of email addresses wnat to be able
    > to select via a query various groups and build a string with all BCC:
    > addresses. This is ok, done.
    >
    > However, while I can trigger outlook express with the shell command, I
    > do not know how to open it with the data in the string in the BCC
    > field.
    >
     
  2. Loading...

    Similar Threads Forum Date
    Microsoft Warning Over Browser Security Flaw Information Technology Feb 1, 2011
    World Microsoft Apologizes for Changing Race in Photo Breaking News Aug 26, 2009
    DOWNLOAD BANI FILES - Microsoft Word Format Discourses in English Feb 11, 2008
    Microsoft Throws Prime-Time Party for Next Xbox (washingtonpost.com) Interfaith Dialogues May 14, 2005
    Microsoft Unveils New Windows Mobile (AP) Interfaith Dialogues May 11, 2005

  3. Smartin

    Smartin
    Expand Collapse
    Guest

    Suzy wrote:
    > Hi there...
    >
    > Could you please explain a little more in detail for this poor newbie?
    > Specifically, how do I get all of my client's email addresses from the table
    > "ClientInfo" in the field "Email Address" into the BCC field?
    >
    > I have made a button and am trying to complete the macro to send an email to
    > all my clients. If I put nothing in the To, CC and BCC fields I get a new
    > email message ready for addressing. No worries there! However I would like
    > all the client's email addresses to be automatically entered into the BCC
    > field and I can't figure how to make the "BCC:=strBCC" that you wrote below
    > work...
    >
    > Instructions for Dummies please... I'm a new user!
    >
    > Thanks,
    > Suzy
    >
    > "Tom Wickerath" wrote:
    >
    >
    >>Hi Simon,
    >>
    >>Instead of using the shell command, perhaps you should look into using
    >>DoCmd.SendObject instead. The ObjectType and ObjectName arguments are
    >>optional, if you don't want to include an attached object.
    >>
    >>Here is an example for sending the results of a query as an attached Excel
    >>spreadsheet:
    >>
    >> DoCmd.SendObject acQuery, "qryManufacturers", _
    >> OutputFormat:=acFormatXLS, _
    >> To:=strTo, CC:=strCC, BCC:=strBCC, _
    >> Subject:=strSubject, EditMessage:=True
    >>
    >>and here is a simplier example, if you don't need to include an attached
    >>object:
    >>
    >> DoCmd.SendObject _
    >> To:=strTo, CC:=strCC, BCC:=strBCC, _
    >> Subject:=strSubject, EditMessage:=True
    >>
    >>
    >>Tom
    >>
    >>http://www.access.qbuilt.com/html/expert_contributors.html
    >>__________________________________________
    >>
    >>"simondhopkin@yahoo.co.uk" wrote:
    >>
    >>Wondering how to trigger Outlook Express from within Access with
    >>preset BCC: addresses
    >>
    >>Have a database containing a list of email addresses wnat to be able
    >>to select via a query various groups and build a string with all BCC:
    >>addresses. This is ok, done.
    >>
    >>However, while I can trigger outlook express with the shell command, I
    >>do not know how to open it with the data in the string in the BCC
    >>field.
    >>


    Here's how I do something a lot like that.

    In the button click event I code among other things (!linewrap):

    Dim BCC As String
    BCC = BCCList()
    DoCmd.SendObject acSendNoObject, , acFormatRTF, Recipients, , BCC,
    Subject, Message.Contents, vbYes


    Now then, BCCList is a function elsewhere. This is the gizmo that builds
    up a list of names from a table, and populates the BCC variable and
    ultimately the BCC field.

    Function BCCList() As String
    ' returns a semicolon-separated list of BCC recipients
    Dim DB As Database
    Dim SQL As String
    Dim RCD As Recordset
    Dim tmp As String
    Set DB = CurrentDb()
    SQL = "SELECT Name FROM BCCRecipients;"
    Set RCD = DB.OpenRecordset(SQL)
    Do While Not RCD.EOF
    tmp = tmp & Trim(RCD.Fields(0).Value) & ";"
    RCD.MoveNext
    Loop
    BCCList = tmp
    End Function

    Table BCCRecipients (your "ClientInfoTable") contains the email
    addresses of the people I want to include in the email as BCC.

    You may need to adjust the SQL and Fields(X) to suit your table.

    HTH!
    --
    Smartin
     
  4. Suzy

    Suzy
    Expand Collapse
    Guest

    I'm getting a "Compile Error: Syntax Error" and it's pointing to this line
    of code:

    DoCmd.SendObject acSendNoObject, , acFormatRTF, Recipients, , BCC,

    Any ideas?

    "Smartin" wrote:

    > Suzy wrote:
    > > Hi there...
    > >
    > > Could you please explain a little more in detail for this poor newbie?
    > > Specifically, how do I get all of my client's email addresses from the table
    > > "ClientInfo" in the field "Email Address" into the BCC field?
    > >
    > > I have made a button and am trying to complete the macro to send an email to
    > > all my clients. If I put nothing in the To, CC and BCC fields I get a new
    > > email message ready for addressing. No worries there! However I would like
    > > all the client's email addresses to be automatically entered into the BCC
    > > field and I can't figure how to make the "BCC:=strBCC" that you wrote below
    > > work...
    > >
    > > Instructions for Dummies please... I'm a new user!
    > >
    > > Thanks,
    > > Suzy
    > >
    > > "Tom Wickerath" wrote:
    > >
    > >
    > >>Hi Simon,
    > >>
    > >>Instead of using the shell command, perhaps you should look into using
    > >>DoCmd.SendObject instead. The ObjectType and ObjectName arguments are
    > >>optional, if you don't want to include an attached object.
    > >>
    > >>Here is an example for sending the results of a query as an attached Excel
    > >>spreadsheet:
    > >>
    > >> DoCmd.SendObject acQuery, "qryManufacturers", _
    > >> OutputFormat:=acFormatXLS, _
    > >> To:=strTo, CC:=strCC, BCC:=strBCC, _
    > >> Subject:=strSubject, EditMessage:=True
    > >>
    > >>and here is a simplier example, if you don't need to include an attached
    > >>object:
    > >>
    > >> DoCmd.SendObject _
    > >> To:=strTo, CC:=strCC, BCC:=strBCC, _
    > >> Subject:=strSubject, EditMessage:=True
    > >>
    > >>
    > >>Tom
    > >>
    > >>http://www.access.qbuilt.com/html/expert_contributors.html
    > >>__________________________________________
    > >>
    > >>"simondhopkin@yahoo.co.uk" wrote:
    > >>
    > >>Wondering how to trigger Outlook Express from within Access with
    > >>preset BCC: addresses
    > >>
    > >>Have a database containing a list of email addresses wnat to be able
    > >>to select via a query various groups and build a string with all BCC:
    > >>addresses. This is ok, done.
    > >>
    > >>However, while I can trigger outlook express with the shell command, I
    > >>do not know how to open it with the data in the string in the BCC
    > >>field.
    > >>

    >
    > Here's how I do something a lot like that.
    >
    > In the button click event I code among other things (!linewrap):
    >
    > Dim BCC As String
    > BCC = BCCList()
    > DoCmd.SendObject acSendNoObject, , acFormatRTF, Recipients, , BCC,
    > Subject, Message.Contents, vbYes
    >
    >
    > Now then, BCCList is a function elsewhere. This is the gizmo that builds
    > up a list of names from a table, and populates the BCC variable and
    > ultimately the BCC field.
    >
    > Function BCCList() As String
    > ' returns a semicolon-separated list of BCC recipients
    > Dim DB As Database
    > Dim SQL As String
    > Dim RCD As Recordset
    > Dim tmp As String
    > Set DB = CurrentDb()
    > SQL = "SELECT Name FROM BCCRecipients;"
    > Set RCD = DB.OpenRecordset(SQL)
    > Do While Not RCD.EOF
    > tmp = tmp & Trim(RCD.Fields(0).Value) & ";"
    > RCD.MoveNext
    > Loop
    > BCCList = tmp
    > End Function
    >
    > Table BCCRecipients (your "ClientInfoTable") contains the email
    > addresses of the people I want to include in the email as BCC.
    >
    > You may need to adjust the SQL and Fields(X) to suit your table.
    >
    > HTH!
    > --
    > Smartin
    >
     
  5. Smartin

    Smartin
    Expand Collapse
    Guest

    Suzy wrote:
    > I'm getting a "Compile Error: Syntax Error" and it's pointing to this line
    > of code:
    >
    > DoCmd.SendObject acSendNoObject, , acFormatRTF, Recipients, , BCC,
    >
    > Any ideas?
    >


    Did you beware line wrap in my post? That statement is incomplete, Try

    DoCmd.SendObject acSendNoObject, , acFormatRTF, _
    Recipients, , BCC, Subject, Message.Contents, vbYes

    Hope this helps!
    --
    Smartin
     
  6. Suzy

    Suzy
    Expand Collapse
    Guest

    Apologies... I'm a *real* newbie...

    I've changed the code and I get the same error, but this time only pointing
    to the word Recipients...

    Will I ever get this right?

    "Smartin" wrote:

    > Suzy wrote:
    > > I'm getting a "Compile Error: Syntax Error" and it's pointing to this line
    > > of code:
    > >
    > > DoCmd.SendObject acSendNoObject, , acFormatRTF, Recipients, , BCC,
    > >
    > > Any ideas?
    > >

    >
    > Did you beware line wrap in my post? That statement is incomplete, Try
    >
    > DoCmd.SendObject acSendNoObject, , acFormatRTF, _
    > Recipients, , BCC, Subject, Message.Contents, vbYes
    >
    > Hope this helps!
    > --
    > Smartin
    >
     
  7. Suzy

    Suzy
    Expand Collapse
    Guest

    Sorry, sorry make that I know get "Compile Error: Variable not defined"
    pointing at the word 'Receipients'

    "Smartin" wrote:

    > Suzy wrote:
    > > I'm getting a "Compile Error: Syntax Error" and it's pointing to this line
    > > of code:
    > >
    > > DoCmd.SendObject acSendNoObject, , acFormatRTF, Recipients, , BCC,
    > >
    > > Any ideas?
    > >

    >
    > Did you beware line wrap in my post? That statement is incomplete, Try
    >
    > DoCmd.SendObject acSendNoObject, , acFormatRTF, _
    > Recipients, , BCC, Subject, Message.Contents, vbYes
    >
    > Hope this helps!
    > --
    > Smartin
    >
     
  8. Smartin

    Smartin
    Expand Collapse
    Guest

    Suzy wrote:
    > Sorry, sorry make that I know get "Compile Error: Variable not defined"
    > pointing at the word 'Receipients'
    >
    > "Smartin" wrote:
    >
    >
    >>Suzy wrote:
    >>
    >>>I'm getting a "Compile Error: Syntax Error" and it's pointing to this line
    >>>of code:
    >>>
    >>> DoCmd.SendObject acSendNoObject, , acFormatRTF, Recipients, , BCC,
    >>>
    >>>Any ideas?
    >>>

    >>
    >>Did you beware line wrap in my post? That statement is incomplete, Try
    >>
    >> DoCmd.SendObject acSendNoObject, , acFormatRTF, _
    >> Recipients, , BCC, Subject, Message.Contents, vbYes
    >>
    >>Hope this helps!
    >>--
    >>Smartin
    >>


    "Recipients" is a variable I used that holds the "To" address(es) for
    the email. You could try hard coding those addresses instead of using
    the variable, or set up the variable
    Dim Recipients As String
    and
    Recipients = "Mom@Mom.net; Dad@Dad.net" etc.

    Same goes for "Subject" and "Message.Contents" (except that you would
    probably use a simple string variable "Contents" instead of my oblique
    object reference to Message.Contents).

    Getting there?
    --
    Smartin
     
  9. Suzy

    Suzy
    Expand Collapse
    Guest

    Thanks for your patience! The client database I'll be sending out to has
    600+ records... don't think I want to enter each one...

    Is there something I should be replacing 'Recipients' with? The information
    is in the "ClientInfo" table and named "EmailAddress"

    Boo hoo!

    "Smartin" wrote:

    > Suzy wrote:
    > > Sorry, sorry make that I know get "Compile Error: Variable not defined"
    > > pointing at the word 'Receipients'
    > >
    > > "Smartin" wrote:
    > >
    > >
    > >>Suzy wrote:
    > >>
    > >>>I'm getting a "Compile Error: Syntax Error" and it's pointing to this line
    > >>>of code:
    > >>>
    > >>> DoCmd.SendObject acSendNoObject, , acFormatRTF, Recipients, , BCC,
    > >>>
    > >>>Any ideas?
    > >>>
    > >>
    > >>Did you beware line wrap in my post? That statement is incomplete, Try
    > >>
    > >> DoCmd.SendObject acSendNoObject, , acFormatRTF, _
    > >> Recipients, , BCC, Subject, Message.Contents, vbYes
    > >>
    > >>Hope this helps!
    > >>--
    > >>Smartin
    > >>

    >
    > "Recipients" is a variable I used that holds the "To" address(es) for
    > the email. You could try hard coding those addresses instead of using
    > the variable, or set up the variable
    > Dim Recipients As String
    > and
    > Recipients = "Mom@Mom.net; Dad@Dad.net" etc.
    >
    > Same goes for "Subject" and "Message.Contents" (except that you would
    > probably use a simple string variable "Contents" instead of my oblique
    > object reference to Message.Contents).
    >
    > Getting there?
    > --
    > Smartin
    >
     
  10. xxyy

    xxyy
    Expand Collapse
    Guest

    Suzy <Suzy@discussions.microsoft.com> wrote in message
    news:E5E6241F-75C4-4C91-8D39-BAFF65841691@microsoft.com...
    > Hi there...
    >
    > Could you please explain a little more in detail for this poor newbie?
    > Specifically, how do I get all of my client's email addresses from the

    table
    > "ClientInfo" in the field "Email Address" into the BCC field?
    >
    > I have made a button and am trying to complete the macro to send an email

    to
    > all my clients. If I put nothing in the To, CC and BCC fields I get a new
    > email message ready for addressing. No worries there! However I would

    like
    > all the client's email addresses to be automatically entered into the BCC
    > field and I can't figure how to make the "BCC:=strBCC" that you wrote

    below
    > work...
    >
    > Instructions for Dummies please... I'm a new user!
    >
    > Thanks,
    > Suzy
    >
    > "Tom Wickerath" wrote:
    >
    > > Hi Simon,
    > >
    > > Instead of using the shell command, perhaps you should look into using
    > > DoCmd.SendObject instead. The ObjectType and ObjectName arguments are
    > > optional, if you don't want to include an attached object.
    > >
    > > Here is an example for sending the results of a query as an attached

    Excel
    > > spreadsheet:
    > >
    > > DoCmd.SendObject acQuery, "qryManufacturers", _
    > > OutputFormat:=acFormatXLS, _
    > > To:=strTo, CC:=strCC, BCC:=strBCC, _
    > > Subject:=strSubject, EditMessage:=True
    > >
    > > and here is a simplier example, if you don't need to include an attached
    > > object:
    > >
    > > DoCmd.SendObject _
    > > To:=strTo, CC:=strCC, BCC:=strBCC, _
    > > Subject:=strSubject, EditMessage:=True
    > >
    > >
    > > Tom
    > >
    > > http://www.access.qbuilt.com/html/expert_contributors.html
    > > __________________________________________
    > >
    > > "simondhopkin@yahoo.co.uk" wrote:
    > >
    > > Wondering how to trigger Outlook Express from within Access with
    > > preset BCC: addresses
    > >
    > > Have a database containing a list of email addresses wnat to be able
    > > to select via a query various groups and build a string with all BCC:
    > > addresses. This is ok, done.
    > >
    > > However, while I can trigger outlook express with the shell command, I
    > > do not know how to open it with the data in the string in the BCC
    > > field.
    > >
     
  11. Smartin

    Smartin
    Expand Collapse
    Guest

    Suzy wrote:
    > Thanks for your patience! The client database I'll be sending out to has
    > 600+ records... don't think I want to enter each one...
    >
    > Is there something I should be replacing 'Recipients' with? The information
    > is in the "ClientInfo" table and named "EmailAddress"
    >
    > Boo hoo!
    >


    600 records? (!)

    No need to enter each one (assuming the email addresses are already
    there) since your client records are already in a table and the
    procedure I gave you will enumerate all the records. Although, I
    hesitate to think that the DoCmd object will support a string of 600+
    email BCC addresses (is this what you meant to do?).

    Again, "Recipients" is just a variable name I was using. If your
    recipient email addresses are all handily in a table you can set up
    "Recipients" with something like

    Dim Recipients as String
    Recipients = AddressList()

    where

    Function AddressList() As String
    ... a function very similar to the BCC function except that it points
    to the "ClientInfo" table...

    Hope this helps!
    --
    Smartin
     
  12. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Suzy,

    Wow, lots of activity on this thread--I just got home from work and read
    through the posts. Have you had success yet using Smartin's help? I didn't
    include this in my original post on this thread, but you might want to use
    the Nz function to convert any nulls into zero length strings. For example:

    DoCmd.SendObject _
    To:=Nz(strTo,""), CC:=Nz(strCC,""), BCC:=Nz(strBCC,""), _
    Subject:=strSubject, EditMessage:=True


    The code that Smartin gave you in his first reply for the BCCList function
    requires that you have a reference set to the "Microsoft DAO 3.6 Object
    Library", since this is DAO code. (Use the 3.6 Object Library for Access 2000
    or later; use the 3.51 Object Library for Access 97). In addition, I would
    like to offer some improvements to his code:

    Function BCCList() As String
    ' returns a semicolon-separated list of BCC recipients
    Dim DB As Database
    Dim SQL As String
    Dim RCD As Recordset
    Dim tmp As String
    Set DB = CurrentDb()
    SQL = "SELECT Name FROM BCCRecipients;"
    Set RCD = DB.OpenRecordset(SQL)
    Do While Not RCD.EOF
    tmp = tmp & Trim(RCD.Fields(0).Value) & ";"
    RCD.MoveNext
    Loop
    BCCList = tmp
    End Function


    1.) All procedures should include error handling
    2.) Use explicit declarations for recordsets (and any other methods that are
    common to the ADO library). Here is an article that I wrote that discusses
    this issue:

    ADO and DAO Library References in Access Databases
    http://www.access.qbuilt.com/html/gem_tips1.html

    3.) To prevent database bloat, make sure to close objects that you open:

    http://tinyurl.com/2dmpw

    and more specifically:
    How to prevent database bloat after you use Data Access Objects (DAO)
    http://support.microsoft.com/?id=289562

    4.) Name is a reserved word (SQL = "SELECT Name FROM BCCRecipients;"). You
    should avoid using reserved words for anything that you assign a name to in
    Access.

    Reserved Words in Microsoft Access
    http://support.microsoft.com/?id=286335

    List of reserved words in Jet 4.0
    http://support.microsoft.com/?id=321266

    5.) You should avoid using spaces (Email Address) in anything you assign a
    name to in Access. You'll need to make sure to add square brackets [ ] in any
    VBA code that includes an object name with spaces. Similarily, square
    brackets may be needed in some situations if you use reserved words. My
    advice is to avoid both potential headaches.

    Special characters that you must avoid when you work with Access databases
    http://support.microsoft.com/?id=826763


    Combining all these ideas together, we have the following two procedures in
    a code module associated with a form. The form includes a command button
    named "cmdSendMessage" (without the quotes). The form includes the following
    four text boxes: txtToEmailAddress, txtCCEmailAddress, txtSubject and
    txtMessage. You can set default values for these text boxes if you'd like, or
    you can omit the text boxes and hard code the values into the VBA code. The
    e-mail addresses from your table will be added to the BCC field.

    You might run into an issue with your ISP (Internet Service Provider) where
    they reject e-mail messages that include spam characteristics, specifically a
    very long list of e-mail addresses. In that case, you might want to look into
    using an alternate method. Here is the revised code:

    '***************Begin Code*********************

    Option Compare Database
    Option Explicit

    Private Sub cmdSendMessage_Click()
    On Error GoTo ProcError

    Dim strTo As String
    Dim strCC As String
    Dim strSubject As String
    Dim strMessage As String

    strTo = Nz(Me.txtToEmailAddress, "")
    strCC = Nz(Me.txtCCEmailAddress, "")
    strSubject = Nz(Me.txtSubject, "")
    strMessage = Nz(Me.txtMessage, "")

    DoCmd.SendObject _
    To:=strTo, CC:=strCC, BCC:=Nz(BCCList, ""), _
    Subject:=strSubject, MessageText:=strMessage, EditMessage:=True


    ExitProc:
    Exit Sub
    ProcError:
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
    vbCritical, "Error in procedure cmdSendMessage_Click..."
    Resume ExitProc
    End Sub

    Function BCCList() As String
    On Error GoTo ProcError

    ' returns a semicolon-separated list of BCC recipients
    Dim DB As Database
    Dim SQL As String
    Dim RCD As DAO.Recordset
    Dim tmp As String

    Set DB = CurrentDb()
    SQL = "SELECT [Email Address] FROM ClientInfo;"

    Set RCD = DB.OpenRecordset(SQL)

    Do While Not RCD.EOF
    tmp = tmp & Trim(RCD.Fields("Email Address").Value) & ";"
    RCD.MoveNext
    Loop

    BCCList = tmp
    Debug.Print BCCList
    ExitProc:
    On Error Resume Next 'Cleanup
    RCD.Close: Set RCD = Nothing
    DB.Close: Set DB = Nothing
    Exit Function
    ProcError:
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
    vbCritical, "Error in procedure BCCList..."
    Resume ExitProc

    End Function


    '***************End Code**********************

    Good luck, and please let us know how it goes. Don't forget to set that
    required reference to the DAO Object Library. Also, click on Debug > Compile
    ProjectName before attempting to run the code. Correct any compile errors
    that you might get first.

    Tom

    http://www.access.qbuilt.com/html/expert_contributors.html
    __________________________________________

    "Suzy" wrote:

    Thanks for your patience! The client database I'll be sending out to has
    600+ records... don't think I want to enter each one...

    Is there something I should be replacing 'Recipients' with? The information
    is in the "ClientInfo" table and named "EmailAddress"

    Boo hoo!
     
  13. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    PS. Remove the Debug.Print BCCList that I left in the code.

    ____________________________________________

    "Tom Wickerath" wrote:

    > Hi Suzy,
    >
    > Wow, lots of activity on this thread--I just got home from work and read
    > through the posts. Have you had success yet using Smartin's help? I didn't
    > include this in my original post on this thread, but you might want to use
    > the Nz function to convert any nulls into zero length strings. For example:
    >
    > DoCmd.SendObject _
    > To:=Nz(strTo,""), CC:=Nz(strCC,""), BCC:=Nz(strBCC,""), _
    > Subject:=strSubject, EditMessage:=True
    >
    >
    > The code that Smartin gave you in his first reply for the BCCList function
    > requires that you have a reference set to the "Microsoft DAO 3.6 Object
    > Library", since this is DAO code. (Use the 3.6 Object Library for Access 2000
    > or later; use the 3.51 Object Library for Access 97). In addition, I would
    > like to offer some improvements to his code:
    >
    > Function BCCList() As String
    > ' returns a semicolon-separated list of BCC recipients
    > Dim DB As Database
    > Dim SQL As String
    > Dim RCD As Recordset
    > Dim tmp As String
    > Set DB = CurrentDb()
    > SQL = "SELECT Name FROM BCCRecipients;"
    > Set RCD = DB.OpenRecordset(SQL)
    > Do While Not RCD.EOF
    > tmp = tmp & Trim(RCD.Fields(0).Value) & ";"
    > RCD.MoveNext
    > Loop
    > BCCList = tmp
    > End Function
    >
    >
    > 1.) All procedures should include error handling
    > 2.) Use explicit declarations for recordsets (and any other methods that are
    > common to the ADO library). Here is an article that I wrote that discusses
    > this issue:
    >
    > ADO and DAO Library References in Access Databases
    > http://www.access.qbuilt.com/html/gem_tips1.html
    >
    > 3.) To prevent database bloat, make sure to close objects that you open:
    >
    > http://tinyurl.com/2dmpw
    >
    > and more specifically:
    > How to prevent database bloat after you use Data Access Objects (DAO)
    > http://support.microsoft.com/?id=289562
    >
    > 4.) Name is a reserved word (SQL = "SELECT Name FROM BCCRecipients;"). You
    > should avoid using reserved words for anything that you assign a name to in
    > Access.
    >
    > Reserved Words in Microsoft Access
    > http://support.microsoft.com/?id=286335
    >
    > List of reserved words in Jet 4.0
    > http://support.microsoft.com/?id=321266
    >
    > 5.) You should avoid using spaces (Email Address) in anything you assign a
    > name to in Access. You'll need to make sure to add square brackets [ ] in any
    > VBA code that includes an object name with spaces. Similarily, square
    > brackets may be needed in some situations if you use reserved words. My
    > advice is to avoid both potential headaches.
    >
    > Special characters that you must avoid when you work with Access databases
    > http://support.microsoft.com/?id=826763
    >
    >
    > Combining all these ideas together, we have the following two procedures in
    > a code module associated with a form. The form includes a command button
    > named "cmdSendMessage" (without the quotes). The form includes the following
    > four text boxes: txtToEmailAddress, txtCCEmailAddress, txtSubject and
    > txtMessage. You can set default values for these text boxes if you'd like, or
    > you can omit the text boxes and hard code the values into the VBA code. The
    > e-mail addresses from your table will be added to the BCC field.
    >
    > You might run into an issue with your ISP (Internet Service Provider) where
    > they reject e-mail messages that include spam characteristics, specifically a
    > very long list of e-mail addresses. In that case, you might want to look into
    > using an alternate method. Here is the revised code:
    >
    > '***************Begin Code*********************
    >
    > Option Compare Database
    > Option Explicit
    >
    > Private Sub cmdSendMessage_Click()
    > On Error GoTo ProcError
    >
    > Dim strTo As String
    > Dim strCC As String
    > Dim strSubject As String
    > Dim strMessage As String
    >
    > strTo = Nz(Me.txtToEmailAddress, "")
    > strCC = Nz(Me.txtCCEmailAddress, "")
    > strSubject = Nz(Me.txtSubject, "")
    > strMessage = Nz(Me.txtMessage, "")
    >
    > DoCmd.SendObject _
    > To:=strTo, CC:=strCC, BCC:=Nz(BCCList, ""), _
    > Subject:=strSubject, MessageText:=strMessage, EditMessage:=True
    >
    >
    > ExitProc:
    > Exit Sub
    > ProcError:
    > MsgBox "Error " & Err.Number & ": " & Err.Description, _
    > vbCritical, "Error in procedure cmdSendMessage_Click..."
    > Resume ExitProc
    > End Sub
    >
    > Function BCCList() As String
    > On Error GoTo ProcError
    >
    > ' returns a semicolon-separated list of BCC recipients
    > Dim DB As Database
    > Dim SQL As String
    > Dim RCD As DAO.Recordset
    > Dim tmp As String
    >
    > Set DB = CurrentDb()
    > SQL = "SELECT [Email Address] FROM ClientInfo;"
    >
    > Set RCD = DB.OpenRecordset(SQL)
    >
    > Do While Not RCD.EOF
    > tmp = tmp & Trim(RCD.Fields("Email Address").Value) & ";"
    > RCD.MoveNext
    > Loop
    >
    > BCCList = tmp
    > Debug.Print BCCList
    > ExitProc:
    > On Error Resume Next 'Cleanup
    > RCD.Close: Set RCD = Nothing
    > DB.Close: Set DB = Nothing
    > Exit Function
    > ProcError:
    > MsgBox "Error " & Err.Number & ": " & Err.Description, _
    > vbCritical, "Error in procedure BCCList..."
    > Resume ExitProc
    >
    > End Function
    >
    >
    > '***************End Code**********************
    >
    > Good luck, and please let us know how it goes. Don't forget to set that
    > required reference to the DAO Object Library. Also, click on Debug > Compile
    > ProjectName before attempting to run the code. Correct any compile errors
    > that you might get first.
    >
    > Tom
    >
    > http://www.access.qbuilt.com/html/expert_contributors.html
    > __________________________________________
    >
    > "Suzy" wrote:
    >
    > Thanks for your patience! The client database I'll be sending out to has
    > 600+ records... don't think I want to enter each one...
    >
    > Is there something I should be replacing 'Recipients' with? The information
    > is in the "ClientInfo" table and named "EmailAddress"
    >
    > Boo hoo!
     
  14. Colin Hammond

    Colin Hammond
    Expand Collapse
    Guest

    Suzy
    As a beginner you might like to try the procedure another beginner uses.

    a.. List e-mail addresses using a report facility
    b.. Export to an Excel file using button in tool bar.
    c.. Remove first column and headings so that only the names and e-mails
    remain.
    d.. Add an extra column and fill with a number e.g. 1. This will be used
    to identify imported addresses from others in the address book.
    e.. Open Outlook Express addresses and click on "import other address
    book". Then "Text File comma separated values". Then "Import".
    f.. Identify your CSV file and allocate the columns to appropriate
    Outlook Express values. Allocate the column containing number 1 to "Business
    phone number"
    g.. Press "Finish" to complete transfer of data.
    h.. The e-mail addresses are now in Outlook Express and can be sorted,
    for moving to another folder or deleting, using the 1 in "Business phone
    number"
    "Suzy" <Suzy@discussions.microsoft.com> wrote in message
    news:E5E6241F-75C4-4C91-8D39-BAFF65841691@microsoft.com...
    > Hi there...
    >
    > Could you please explain a little more in detail for this poor newbie?
    > Specifically, how do I get all of my client's email addresses from the
    > table
    > "ClientInfo" in the field "Email Address" into the BCC field?
    >
    > I have made a button and am trying to complete the macro to send an email
    > to
    > all my clients. If I put nothing in the To, CC and BCC fields I get a new
    > email message ready for addressing. No worries there! However I would
    > like
    > all the client's email addresses to be automatically entered into the BCC
    > field and I can't figure how to make the "BCC:=strBCC" that you wrote
    > below
    > work...
    >
    > Instructions for Dummies please... I'm a new user!
    >
    > Thanks,
    > Suzy
    >
    > "Tom Wickerath" wrote:
    >
    >> Hi Simon,
    >>
    >> Instead of using the shell command, perhaps you should look into using
    >> DoCmd.SendObject instead. The ObjectType and ObjectName arguments are
    >> optional, if you don't want to include an attached object.
    >>
    >> Here is an example for sending the results of a query as an attached
    >> Excel
    >> spreadsheet:
    >>
    >> DoCmd.SendObject acQuery, "qryManufacturers", _
    >> OutputFormat:=acFormatXLS, _
    >> To:=strTo, CC:=strCC, BCC:=strBCC, _
    >> Subject:=strSubject, EditMessage:=True
    >>
    >> and here is a simplier example, if you don't need to include an attached
    >> object:
    >>
    >> DoCmd.SendObject _
    >> To:=strTo, CC:=strCC, BCC:=strBCC, _
    >> Subject:=strSubject, EditMessage:=True
    >>
    >>
    >> Tom
    >>
    >> http://www.access.qbuilt.com/html/expert_contributors.html
    >> __________________________________________
    >>
    >> "simondhopkin@yahoo.co.uk" wrote:
    >>
    >> Wondering how to trigger Outlook Express from within Access with
    >> preset BCC: addresses
    >>
    >> Have a database containing a list of email addresses wnat to be able
    >> to select via a query various groups and build a string with all BCC:
    >> addresses. This is ok, done.
    >>
    >> However, while I can trigger outlook express with the shell command, I
    >> do not know how to open it with the data in the string in the BCC
    >> field.
    >>
     
  15. Smartin

    Smartin
    Expand Collapse
    Guest

    Tom Wickerath wrote:
    > Hi Suzy,
    >
    > Wow, lots of activity on this thread--I just got home from work and read
    > through the posts. Have you had success yet using Smartin's help? I didn't
    > include this in my original post on this thread, but you might want to use
    > the Nz function to convert any nulls into zero length strings. For example:


    [snipped]

    Thanks for taking the time to clean up this noob's code (mine, that is)
    (^: I will use it as well.

    --
    Smartin
     
  16. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    You're welcome.

    Tom

    ____________________________________________

    "Smartin" wrote:

    Tom Wickerath wrote:
    > Hi Suzy,
    >
    > Wow, lots of activity on this thread--I just got home from work and read
    > through the posts. Have you had success yet using Smartin's help? I didn't
    > include this in my original post on this thread, but you might want to use
    > the Nz function to convert any nulls into zero length strings. For example:


    [snipped]

    Thanks for taking the time to clean up this noob's code (mine, that is)
    (^: I will use it as well.

    --
    Smartin
     

Share This Page