VB script to send email using outlook contact distribution list

bulleye's Avatar, Join Date: Oct 2006
Newbie Member
Hi everyone,
i am new here and my first post. i'd like to send email from excel using vbcript. how i code it so that the email are sent to the distribution list in my outlook 2003 contacts. below is the codes for sending mail to indivual email addresses. Any help would be appriciated:
Code:
Sub Senmail()
Dim objOutlook As Object
Dim objOutlookMsg As Object
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(0)
With objOutlookMsg
   .To = "name@email.com"
   .Cc = "name@email.com"
   .Subject = "Hello World (one more time)..."
   .Body = "This is the body of message"
   .HTMLBody = "HTML version of message"
   .Attachments.Add ("f:\Test.txt")
   .Send 'Let´s go!
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub
Thanks
Bulleye

Last edited by shabbir; 13Oct2006 at 10:17.. Reason: Code formating.
0
pradeep's Avatar, Join Date: Apr 2005
Team Leader
Ee have to look at each and every item in the Contacts folder and check to see if the item happens to be a distribution list. If it is, we can then bind to that item (the distribution list) and methodically collect all the members and send them a mail.

Code: VB
Const olFolderContacts = 10
  sDistName = "Friends" 'set the distribution list name here
 
  Set objOutlook = CreateObject("Outlook.Application")
  Set objNamespace = objOutlook.GetNamespace("MAPI")
 
  Set colContacts = objNamespace.GetDefaultFolder(olFolderContacts).Items
  intCount = colContacts.Count
 
  For i = 1 To intCount
      If TypeName(colContacts.Item(i)) = "DistListItem" Then
          Set objDistList = colContacts.Item(i)
          sEmails = "";
          If objDistList.DLName = sDistName Then
              For j = 1 To objDistList.MemberCount
                  sEmails = sEmails & ";" & objDistList.GetMember(j).Address
              Next
             
              'send the mail using the emails u got! ;-) enjoy
          End if
      End If
  Next
0
bulleye's Avatar, Join Date: Oct 2006
Newbie Member
Thanks so much for the reply Pradeep. Please excuse my VB ignorance but how can I intergrate your codes to make it work. I added your codes to my and it didn't compile.
0
smileyshowers's Avatar, Join Date: Dec 2006
Newbie Member
could you please tell me what to do to display the body of the mail line by line. and also if we want to display multiple number of items using a forloop..what to do?? thanks for your code for how to send mail from vb
0
pradeep's Avatar, Join Date: Apr 2005
Team Leader
I didnt exactly egt what you want to do, please explain.
0
smileyshowers's Avatar, Join Date: Dec 2006
Newbie Member
When we are sending mail from VB script,
we specify,
with <object>
.To ,
.CC,
.subject,
.body,
.html,
.send

in this, in body, we can enter only text. What I am asking is, I want to get the data in the body of mail line by line. To get line by line, is there any property to be specified in .body section? Also I'm having Array of values. I want to display them in the mail in the body section. How can we do that?

Hope you got my question...

Thanks
smileyshowers
0
pradeep's Avatar, Join Date: Apr 2005
Team Leader
The HTMLBody is optional, you can only specify the text body if you want. To print an array of values, concatenate each array value into a string and then put it in the mail body. And btw, I still didnt get what you mean by want to get the data in the body of mail line by line.
0
smileyshowers's Avatar, Join Date: Dec 2006
Newbie Member
I want the text of the body to be displayed as line by line:
"This is my name.
I am doing job.
I am working."
instead of
"This is my name.I am doing job. I am working." (This is concatenation)
0
pradeep's Avatar, Join Date: Apr 2005
Team Leader
Code: VB
strA = "This is my name."
strB = "I am doing job."

strC = strA & vbCrLf & strB;

This gives:
Code:
This is my name.
I am doing job.
0
smileyshowers's Avatar, Join Date: Dec 2006
Newbie Member
Thanks pradeep.. One more query............
When i use the above code to send mail, after the mail gets triggered from VB, a dialogue box occurs on the desktop asking "Yes","No","Cancel". is there any code that automatically "Yes" must get clicked without user interaction. Can you suggest any solution for that.