Closed Thread
Results 1 to 2 of 2

VBA code to email different attachment to different person using outlook

  1. #1
    Registered User
    Join Date
    hyderabad, india
    MS-Off Ver
    Excel 2003

    VBA code to email different attachment to different person using outlook

    Hello All,

    I am writing a code to automate a work process in my company. In this process i need to mail a respective file to a manager who's looking after a city and after that i have to main another manager a different file corresponding to his region. So this process goes on. We need to mail a manager his corresponding file. All the files to be sent would be in a specific folder(same folder). i have attached the code which i have assemebled from different websie and customized it to suit my requirements. please go through the code and help me in writing VBA code for sending different files to different persons from same folder.

    Sub Mail_Selection_Range_Outlook_Body()
    ' Working in Office 2000-2010
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object

    Set rng = Nothing
    On Error Resume Next
    'Only the visible cells in the selection
    'Set rng = Selection.SpecialCells(xlCellTypeVisible)
    'You can also use a range if you want
    Set rng = Sheets("sheet1").Range("B12:F31").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If rng Is Nothing Then
    MsgBox "The selection is not a range or the sheet is protected" & _
    vbNewLine & "please correct and try again.", vbOKOnly
    Exit Sub
    End If

    With Application
    .EnableEvents = False
    .ScreenUpdating = False
    End With

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    surrfile = Sheets("sheet1").Cells(7, 8)
    With OutMail
    .To = Sheets("sheet1").Cells(5, 8)
    .CC = Sheets("sheet1").Cells(6, 8)
    .BCC = Sheets("sheet1").Cells(7, 8)
    .Subject = Sheets("sheet1").Cells(4, 8)
    .HTMLBody = RangetoHTML(rng)

    '.Attachments.Add "D:\text.txt", olByValue, , _
    "Attachment" ' insert attachment

    ' here i need to attach a file based on the person i am sending the file. For example, if am sending a mail to AGRA city manager, i need to attached the AGRA file from the folder automatically'

    End With
    On Error GoTo 0

    With Application
    .EnableEvents = True
    .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
    End Sub
    Function RangetoHTML(rng As Range)
    ' Changed by Ron de Bruin 28-Oct-2006
    ' Working in Office 2000-2010
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

    'Copy the range and create a new workbook to past the data in
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
    .Cells(1).PasteSpecial Paste:=8
    .Cells(1).PasteSpecial xlPasteValues, , False, False
    .Cells(1).PasteSpecial xlPasteFormats, , False, False
    Application.CutCopyMode = False
    On Error Resume Next
    .DrawingObjects.Visible = True
    On Error GoTo 0
    End With

    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
    SourceType:=xlSourceRange, _
    Filename:=TempFile, _
    Sheet:=TempWB.Sheets(1).Name, _
    Source:=TempWB.Sheets(1).UsedRange.Address, _
    .Publish (True)
    End With

    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
    "align=left x:publishsource=")

    'Close TempWB
    TempWB.Close savechanges:=False

    'Delete the htm file we used in this function
    Kill TempFile

    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
    End Function

  2. #2
    Forum Expert pike's Avatar
    Join Date
    Alstonville, Australia
    MS-Off Ver

    Re: VBA code to email different attachment to different person using outlook

    Hi rav4u_tom
    welcome to the forum but
    This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

    Thread Closed.

    you can "bump" the other thread are a day or so
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1