Consulting

Results 1 to 11 of 11

Thread: Automation of Mails based on the excel list

  1. #1
    VBAX Regular
    Joined
    Jan 2012
    Location
    INDIA
    Posts
    45
    Location

    Automation of Mails based on the excel list

    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.
    Attached Files Attached Files

  2. #2
    VBAX Regular
    Joined
    Jan 2012
    Location
    INDIA
    Posts
    45
    Location
    Hi Team,

    Please help me on the above code...

    Regards,
    BVSR

  3. #3
    VBAX Regular
    Joined
    Jan 2012
    Location
    INDIA
    Posts
    45
    Location
    hi team, please help me.

  4. #4
    VBAX Regular
    Joined
    Jan 2012
    Location
    INDIA
    Posts
    45
    Location
    Hi,

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

    Regards
    Surya

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [vba]
    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[/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    VBAX Regular
    Joined
    Jan 2012
    Location
    INDIA
    Posts
    45
    Location
    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"

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    You can handle those changes can you not? Not that my code starts the i loop at 4, it should be 2.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Regular
    Joined
    Jan 2012
    Location
    INDIA
    Posts
    45
    Location
    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

  9. #9
    VBAX Regular
    Joined
    Jan 2012
    Location
    INDIA
    Posts
    45
    Location
    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

  10. #10
    VBAX Regular
    Joined
    Jan 2012
    Location
    INDIA
    Posts
    45
    Location
    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

  11. #11
    VBAX Newbie
    Joined
    Apr 2012
    Posts
    1
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •