Emailing A List of People using VBA

Hi n_s_simpson

Look here for Examples
http://www.rondebruin.nl/sendmail.htm

See also the tips link on each page


--
Regards Ron de Bruin
http://www.rondebruin.nl


"n_s_simpson" <[email protected]> wrote in message
news:[email protected]...
>
> Hi People
>
> I am trying to use the following code (you'll need to set up References
> within the VB screen if you want to try this code):
>
>>
>> Public objOutlook As Object
>> Public objNS As Outlook.Namespace
>> Public objMsg As Outlook.MailItem
>> Public objRecipient As Outlook.Recipient
>> Public strCurrentUser As String
>> Public strCurrentUserFull As String
>>
>> Sub MailTest2()
>> Dim blnDone As Boolean
>> Dim x As Integer
>> Dim strSignature As String
>> Dim strReportDrive As String
>> Dim strReportDir As String
>> Set objOutlook = GetObject(, "Outlook.Application")
>> Set objNS = objOutlook.GetNamespace("MAPI")
>> Set objMsg = objNS.Application.CreateItem(olMailItem)
>> With objMsg
>> .Display
>> Set objRecipient = .Recipients.Add("Undisclosed Recipients")
>> objRecipient.Type = olCC
>> Set objRecipient =
>> .Recipients.Add("[email protected];[email protected]")
>> objRecipient.Type = olBCC
>> .Subject = " This is a test"
>> .Body = "This test should work"
>> .Attachments.Add "L:\whatever.xls", , intChar + 1
>> .Body = .Body
>> .Send
>> End With
>>
>> Set objRecipient = Nothing
>> Set objMsg = Nothing
>> Set objNS = Nothing
>> Set objOutlook = Nothing
>> MsgBox "EMAILS SENT!!!"
>> End Sub
>>
>
> Questions:
>
> 1) Why do the email addresses not go into the correct bits of outlook?
> ie setting the BCC doesn't seem to work. however if I type
>
> Set objRecipient =
> Recipients.Add("[email protected];[email protected]")
> objRecipient.Type = olBCC
> Set objRecipient =
> Recipients.Add("[email protected];[email protected]")
> objRecipient.Type = olBCC
>
> then the second address line goes into the BCC field but the first line
> goes into the To field.
> The Undisclosed Recipients address part does go into the To field as
> required but I've seen this go elsewhere while trying to figure this
> out.
>
> 2) How do I alter my program to surpress the security warning that keep
> popping up?
>
> Thanks
>
> Nick
>
>
> --
> n_s_simpson
> ------------------------------------------------------------------------
> n_s_simpson's Profile: http://www.excelforum.com/member.php...o&userid=31859
> View this thread: http://www.excelforum.com/showthread...hreadid=515868
>

Ron, you are an absolute genius!!! I’ve been trying to work out how to do this for a couple of days now and you’ve answered my query in a matter of seconds.

Thanks for your excellent guidance.

Nick