Sending Email with attachment

[santhoshkunder]

Hi All,

Here is a solution that exactly matches my requirement on splitting the file. This split the files based on sale person provided in the column(http://chandoo.org/wp/2011/10/19/split-excel-file-into-many/). After the file is split i want to send out the emails to the respective sales person email id.(I have created a column in attached file) with the attachment taking from a folder where the split took place and a common email body. Could some one help me to achieve this?.
Thanks in advance.
santhosha

 

[Deepak]

Check this...

Option Explicit

Sub breakMyList()
    ' This macro takes values in the range myList
    ' and breaks it in to multiple lists
    ' and saves them to separate files.
   
    Dim cell As Range
    Dim curPath As String, strpath As String
   
    curPath = ActiveWorkbook.Path & "\"
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    For Each cell In Range("lstSalesman")
        [valSalesman] = cell.Value
        Range("myList").AdvancedFilter Action:=xlFilterCopy, _
            criteriarange:=Range("Criteria"), copyToRange:=Range("Extract"), unique:=False
        Range(Range("Extract"), Range("Extract").End(xlDown)).Copy
        Workbooks.Add
        ActiveSheet.Paste
        strpath = curPath & cell.Value & Format(Now, "dmmmyyyy-hhmmss") & ".xlsx"
        ActiveWorkbook.SaveAs Filename:=strpath, _
            FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWindow.Close
        Range(Range("Extract"), Range("Extract").End(xlDown)).ClearContents
       
          With CreateObject("Outlook.Application").CreateItem(0)
            .To = ""
            .CC = ""
          ' .BCC = ""
            .Subject = cell
            .HTMLBody = "pls find"
            .Attachments.Add strpath
            .Display
            '.Send
        End With
       
       
    Next cell
   
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
   
End Sub

 

[santhoshkunder]

Deepak. Thank you for your swift reply but unfortunately it is not working as i intended. What i need to do is sending the file based the email id provided in one of the column. It should pick the exact file and send it the respective users. The solution you gave me just create the email template for me but not sending the emails. As a whole it should be something like this. Code should split the file and then an email should be sent to sale person with their respective file. Thanks.

 

[Deepak]

Check it.

 

[santhoshkunder]

This works with one exception. I have to click send button in outlook which is very tedious as i have around 150 emails to send out every week. Can we automate that too?

 

[Deepak]

replace these two

'.Display
.Send

 

[santhoshkunder]

You are amazing. Now It is perfectly all right.

 

[Deepak]

Good to know that.