Click here to Skip to main content
12,458,439 members (56,563 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB VB.NET
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 15-Nov-12 20:05pm
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160826.1 | Last Updated 16 Nov 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100