Automation of Mails based on the excel list

[bvsramesh]

Hi Team,

Please find the attached documents and a mail format.in the attachement i have two excel documents consists

1) Proposed matches : a list of match groups which need to be either approved or to be dissolved before a cut of time. for that we need to send the mails based on the "Dept" column.

2) Department Ownership : this file consists for owners of the "Dept"

3) the Mail is an example of how we are sending the mails to the specific dept owners


From the above details, is there any possibility that we can automate the process of sending of the mails to the respective users. because i am giving only few Depts and Proposed matches as i am supposed to send almost 100+ mails to the respective users which will take almost 2 to 3 hours to complete.

Please help me out this.


Regards

Surya.

[bvsramesh]

Hi Team,

Please help me on the above code...

Regards,
BVSR

[bvsramesh]

hi team, please help me.

[bvsramesh]

Hi,

Please can i get the help on the above Thread... plz help me....

Regards
Surya

[Bob Phillips]

Sub SendMail()
Dim wbDept As Workbook
Dim wsDept As Worksheet
Dim wbMatches As Workbook
Dim wsMatches As Worksheet
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim bodyText As String
Dim lastDept As Long
Dim lastMatch As Long
Dim i As Long, j As Long

Set wbDept = Workbooks("Department Ownership.xls")
Set wsDept = wbDept.Worksheets("Department Ownership")

Set wbMatches = Workbooks("Proposed Matches.xls")
Set wsMatches = wbMatches.Worksheets("Proposed Matches")

lastDept = wsDept.Cells(wsDept.Rows.Count, "A").End(xlUp).Row
lastMatch = wsMatches.Cells(wsMatches.Rows.Count, "A").End(xlUp).Row

Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True

For i = 4 To lastDept

Set oMailItem = oOutlook.CreateItem(0)
With oMailItem

Set oRecipient = .Recipients.Add(wsDept.Cells(i, "C").Value)
oRecipient.Type = 1 '1 = To, use 2 for cc
Set oRecipient = .Recipients.Add(wsDept.Cells(i, "D").Value)
oRecipient.Type = 1
.Subject = "PLEASE APPROVE : Proposed matches to be broken today at 11 a.m. London time - For Dept Tag : TRIP"
bodyText = "Hi Team," & vbNewLine & vbNewLine & _
"Please note that we will break the Proposed Matches dated " & _
"19th March 2012 or earlier for the Dept Tag TRIP." & vbNewLine & vbNewLine & _
"So please Approve the Proposed Match Groups in the below " & _
"Balance Pool (s) before 11:00 a.m London Time." & vbNewLine & vbNewLine & _
"MatchId MatchDate Pass Company Pool Dept (Un)Locked" & vbNewLine

For j = 2 To lastMatch

If wsMatches.Cells(j, "D").Value = wsDept.Cells(i, "A").Value Then

bodyText = bodyText & wsMatches.Cells(j, "A").Text & vbTab
bodyText = bodyText & wsMatches.Cells(j, "B").Text & vbTab
bodyText = bodyText & wsMatches.Cells(j, "C").Text & vbTab
bodyText = bodyText & wsMatches.Cells(j, "D").Text & vbTab
bodyText = bodyText & wsMatches.Cells(j, "E").Text & vbTab
bodyText = bodyText & wsMatches.Cells(j, "F").Text & vbTab
bodyText = bodyText & wsMatches.Cells(j, "G").Text & vbNewLine
End If
Next j

.body = bodyText & vbNewLine & vbNewLine & vbNewLine & _
"Best Regards" & vbNewLine & vbNewLine & _
"B.V.S.Ramesh" & vbNewLine & _
"Operations Utilities"
.Display
End With
Next i
End Sub

[bvsramesh]

Hi Team,

Thank you very much... Xld Sir ... This is super and i never forget your help..

I need little modifications in the code as the body of the text is copying the values of from the source file Proposed Matches.xls but this will results if the mail will be huge.
eg: if the column "F" from the above source file contains more than 500 lines (because i am sending the mail based on the "dept") in this case the mail size will huge and it very difficult to read the mail.

Note :

from the above code i have changed because i need to send mails by dept value.

"if wsMatches.Cells(j, "D").Value = wsDept.Cells(i, "A").Value "

from the above code, i have changed small changes like the below

" If wsMatches.Cells(j, "F").Value = wsDept.Cells(i, "B").Value Then"


1 ) I need to send a excel attachment for that particulat range. (Dept value) instead of sending the values from the source file.

2) I have seen in the "Subject", the Subject line is common but in the last the Dept value is should be dirrerent for each mail

eg: if we are sending to mail for Dept value "TRIP"

"PLEASE APPROVE : Proposed matches to be broken today at 11 a.m. London time - For Dept Tag : TRIP"

but when i sending to Dept value "CCFC" the Subject line should be change as

"PLEASE APPROVE : Proposed matches to be broken today at 11 a.m. London time - For Dept Tag : CCFC"

[Bob Phillips]

You can handle those changes can you not? Not that my code starts the i loop at 4, it should be 2.

[bvsramesh]

Hi,

plz proved me the code for "Subject" and i will try for the body of the mail. because i am not that much expert.

Thank you.

BVSR

[bvsramesh]

Hi Team,

Please help me as i am not able to solve that and i didnt get any idea for the above both "Subject" and "Attachment" . so Please help me... plz.


Regards and Thanks in advance,

BVSR

[bvsramesh]

Hi,

all excel sheets were already attached in the first request.. in the zip file.

all i need from the above code which was provide by Xld, i need the above said modifications.

regards,

bvsr

[Broro183]

NOTE: I am a new poster in VBAExpress which means I can not post links, so I have just included them as text. To use the text as a URL just add "www" to the start of each web address.

Cross posted as .thecodecage.com/forumz/microsoft-excel-forum/212259-automation-out-look-mails-based-excel-list.html

I have had a go at providing a solution in post # 10 of the above thread.

Surya,
Can you please provide feedback to Xld & I on our respective suggestions?
Also, can you please take the time to read .excelguru.ca/content.php?184 ?

Rob