Click here to Skip to main content
15,892,697 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
VB
Sub SendEmail()
    Dim OutlookApp As Object
    Dim MItem As Object
    Dim cell As Range
    Dim email_ As String
    Dim cc_ As String
    Dim subject_ As String
    Dim body_ As String
    Dim body1_ As String
    Dim body2_ As String
    Dim body3_ As String
    Dim body4_ As String
    Dim body5_ As String
    Dim body6_ As String
    Dim body7_ As String
    Application.DisplayAlerts = False 'Disable all the Alerts from excel
    Application.ScreenUpdating = False 'After opening Word Doc, Document will not be visible
    'Create a New Object for Microsoft Word Application
    Dim objWord As String
    'Create a New Word Document Object
    Dim objDoc As Object
    
    'Open a Word Document and Set it to the newly created object above
            objWord = Application.GetOpenFileName(Title:="Select MS Word " & "file to mail, then click 'Open'", buttontext:="Send", MultiSelect:=False)
            objDoc  = GetObject(objWord)
            body7_  = objDoc.Range(Start:=objDoc.Paragraphs(1).Range.Start, _
    End:=objDoc.Paragraphs(objDoc.Paragraphs.Count).Range.End)

    'Set objDoc = objWord.documents.Open("C:\Documents and Settings\tiny563\Desktop\VCE Testing.docx")
    'To Store all the content of that word Document in a variable
    'body7_ = objDoc.Range(0, objDoc.Range.End)
    
     'Create  Outlook object
    Set OutlookApp = CreateObject("Outlook.Application")
     
     ' Loop through the rows
    For Each cell In Columns("a").Cells.SpecialCells(xlCellTypeConstants)
         
        email_ = cell.Value
        cc_ = cell.Offset(0, 1).Value
        subject_ = cell.Offset(0, 2).Value
        body1_ = cell.Offset(0, 3).Value
        body2_ = cell.Offset(0, 4).Value
        body3_ = cell.Offset(0, 5).Value
        body4_ = cell.Offset(0, 6).Value
        body5_ = cell.Offset(0, 7).Value
        body6_ = cell.Offset(0, 8).Value
        'body7_ = cell.Offset(0, 9).Value
        
        body_ = body1_ & " " & body2_ & vbLf & vbLf & body3_ & body4_ & vbLf & body5_ & vbLf & body6_ & vbLf & body7_
     'Create Mail Item and send it
        Set MItem = OutlookApp.CreateItem(0)
        With MItem
            .To = email_
            .CC = cc_
            .Subject = subject_
            .Body = body_
            .Display
            '.Send
        End With
    Next
    
    objDoc.Close SaveChanges:=wdDoNotSaveChanges
    objWord.Quit
End Sub


[edit]Code block added - OriginalGriff[/edit]
Posted
Updated 15-Nov-12 21:20pm
v2
Comments
OriginalGriff 16-Nov-12 3:20am    
What error are you getting?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900