Something to Share for a little help

[Waltons]

Thanks to everyone who has posted on this forum there is a lot of useful code in here
though sometimes what we need is just out of reach.
My previous post recieved no replies so I did some more research on the forum and found a solution to html in the body of the email. Thank you to AntonyX for the html in the body of an email.

I have created an address book of contacts to allow my users to use a global address book rather then individual contact list in outlook 2000. Posted below is the code that AntonyX posted and has been modified to suit my need, I just need a little help to add a loop to BCC and possibly a better method other then a make table query to get the selected email list.

At the moment I have seperated my single contact list table into a multiple tab form of contacts seperated into their catagories of government, wholesale, retail, public etc. on each of the tabs is a seperate subform with each catagory. I have a single email button that grabs one selected contact and generates a generic email. I would like the second button to grab the selected ticked check boxs and also add them to the list of BCC.

Posted below is the code from AntonyX I have modified. The code shows basically how the html works with the references needed and the different types of html I used in the body. It also shows the make table code and where I need the loop help. The other help I was wondering about is if their is a better way then a make table query to get the selected checkboxs ticked and add their email address to the BCC of the email below.

I hope I can get some help with this it would be greatly appreciated.

Private Sub cmdEmailAllButton_Click()
    
    ' This is the make table query that selects the updated check box of the email
    'addresses to be included in the BCC of the email
    
    Dim strDocName As String
    strDocName = "tblEmailIncluded"
    DoCmd.OpenQuery "qryEmailIncluded"

'References: Oulook Object Library

Dim strEmail, strSubject As String, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

strEmail = Me.EmailAddress '<---- This is where I need help to the loop to add the emails to BCC
            
            'HTML References:
            'Each line of code must be encapsulated into the "" brackets
            ' & _   =   must be placed at the end of each closing " bracket
            '<br>   =   a line break
            '<u>    =   Starts the html underline tag used for hyperlinks
            '</u>   =   Closes the underline statement for mailto: and http://
            
            
            'When pasting code from a html editor you will need to change all "" comments
            'to single ' as the code will not work without them. You will also need to start
            'and end each line of code with " & " followed by a [space]&[space]_
            
            
strBody = "<img border='0' src='C:/Botanica/Header.jpg' width='784' height='153'><br>" & _
        "<br>" & _
        "<br>" & _
        "<font size='2' face='Tahoma' colour='#000000'>Dear Customers,<br>" & _
        "<br>" & _
        "<font size='2' face='Tahoma' colour='#000000'>Please find attached to this e-mail, information regarding to stock availability.<br>" & _
        "<br>" & _
        "<font size='2' face='Tahoma' colour='#000000'>Kind regards,<br>" & _
        "<br>" & _
        "<font size='2' face='Tahoma' colour='#000000'>Botanica<br>" & _
        "<br>" & _
        "<br>" & _
        "<span style='font-family: Arial; font-size: 10pt; color: #5A79B5'>  <u>email@withheld.com.au</u></span><br>" & _
        "<span style='font-family: Arial; font-size: 10pt; color: #5A79B5'>  <u>www.ourwebsite.com.au</u></span><br>" & _
        "<br>" & _
        "<DIV class='MsoNormal' style='TEXT-ALIGN: center' align='left'><HR align='center' width='700' SIZE='2'></DIV><br>" & _
        "<br>" & _
        "<font size='1' face='Tahoma' colour='#000000'>Address line 1<br>" & _
        "<font size='1' face='Tahoma' colour='#000000'>Address line 2<br>" & _
        "<font size='1' face='Tahoma' colour='#000000'>Address line 3<br>" & _
        "<br>"

strSubject = "Our Stock availablity"

With objEmail
    .To = ""
    .CC = ""
    .BCC = strEmail '<-------Everyone's emails here not just the selected check box
    .Subject = strSubject
    .HTMLBody = strBody 'This loads the strBody html info
    '.Send 'Will cause the outlook warning message of an email trying to be sent
    .DISPLAY
End With

'Set objEmail = Nothing 'This clears the email message body after sending not the email itself
End Sub

Thanks in advance

Kevin

 

[Waltons]

Loop Found

I used the following post to create my loop.

<----Click Here---->

With this loop added to the other code I achieved my goal for the help I needed.
Once again thank to all who have posted on this forum I hope this code helps anyone with the same problem in the future, and just remember if you persist on this forum and with your code you can achieve the results you need.

    Dim strEmail, strSubject As String, strBody As String
    Dim objOutlook As Outlook.Application
    Dim objEmail As Outlook.MailItem
    [COLOR="Blue"]Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset[/COLOR]
   
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
[COLOR="blue"]Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset

    rs.Open "tblEmailIncluded", cn
    
   With rs
        Do While Not .EOF
        strEmail = strEmail & .Fields("EmailAddress") & "; "
        
                .MoveNext
        Loop
        .Close
End With[/COLOR]
' strEmail = Me.EmailAddress '<---- seems I needed more then just this
[COLOR="blue"]strEmail = LEFT(strEmail, Len(strEmail) - 1)[/COLOR]

Free Your Mind!

Kev.