edit outlook email based on values in excel

[drewbny]

Each month i have a standard email in outlook that i write, but i have to change a bunch of numbers in the text based on data i have in excel. can i automate that process?

 

[NateSC]

Yes it is possible. Here is one I "wrote" to create an email from scratch from a button push in Excel. I found most of this on the internet and then adjusted it for my needs so feel free to modify it for your use. Basically it looks through some rows in excel, builds a collection of information, builds an email from scratch and outputs the collection one by one into unique emails. I stopped short of having it hit send so I could review the emails, but that is pretty easy as I remember. I hope this gives you a starting point.

Sub SendForChanges()

Dim colFiles As Collection
Dim changeDoc As New clsDoc ' a class variable I created
Dim myDoc As clsDoc ' a class variable I created

Dim bStarted As Boolean
Dim oOutlookApp As Object
Dim oItem As Object
Dim pStr As String
    
    On Error Resume Next

    myExcel = ActiveWorkbook.Name
    myPath = ActiveWorkbook.Path
    myName = Workbooks(myExcel).Sheets("Changes List").Range("F1").Value
    
    Set colFiles = New Collection
    
    'Get list of docs and changes from Excel
    myRows = Cells(Rows.Count, 2).End(xlUp).Row
    For i = 1 To myRows
        If LCase(Range("A" & i).Value) = "x" Then
            Set changeDoc = New clsDoc
            changeDoc.Name = Range("B" & i).Value
            changeDoc.Changes = Range("C" & i).Value
            changeDoc.Rev = Range("D" & i).Value
            colFiles.Add changeDoc
        End If
    Next i
        
    ' Loop through the collection and output the information
    For Each myDoc In colFiles
        
            'Get Outlook if it's running
            Set oOutlookApp = GetObject(, "Outlook.Application")
        '    If Err <> 0 Then
        '        'Outlook wasn't running, start it from code
        '        Set oOutlookApp = CreateObject("Outlook.Application")
        '        bStarted = True
        '    End If
        
            'Create a new mailitem
            Set oItem = oOutlookApp.CreateItem(olMailItem)
        
        
            With oItem
                .CC = ""
                .BCC = ""
                .Subject = myDoc.Name & " revision"
                .Body = "Please see the attached " & myDoc.Name & ".  Please italicize all changes that you make to the document." & _
                    Chr(10) & "Please provide a simple bulleted list of the changes."
                .Body = .Body & Chr(10) & Chr(10)
                .Body = .Body & "Sincerely," & Chr(10) & myName
                .Display
            End With
                
        '    If bStarted Then
        '        'If we started Outlook from code, then close it
        '        oOutlookApp.Quit
        '    End If
        
            'Clean up
            Set oItem = Nothing
            Set oOutlookApp = Nothing
    
    Next myDoc

End Sub

 

[drewbny]

i ran into a compiler error

 

[NateSC]

My code has a Class Variable that you would need to take out. Also, that code is specific to the instance I was working on. You will have to change the code to meet your requirements. Specifically, you will have to write the section that reads the data from the correct Excel references and then change the output text for your email.

I only provided this to give you the pieces needed to make this work. It isn't a drop-in because you haven't provided enough information to write a drop-in.

 

[drewbny]

My code has a Class Variable that you would need to take out. Also, that code is specific to the instance I was working on. You will have to change the code to meet your requirements. Specifically, you will have to write the section that reads the data from the correct Excel references and then change the output text for your email.

I only provided this to give you the pieces needed to make this work. It isn't a drop-in because you haven't provided enough information to write a drop-in.

what is a drop-in?

 

[NateSC]

Sorry. I just meant you can't copy and paste my code directly into your code window and expect it to work (ie you can't drop it in). It has the pieces you need, but it needs to be modified to work for your case. You need to update where the data is pulled from and you need to formulate the email as well.

 

[drewbny]

Sorry. I just meant you can't copy and paste my code directly into your code window and expect it to work (ie you can't drop it in). It has the pieces you need, but it needs to be modified to work for your case. You need to update where the data is pulled from and you need to formulate the email as well.

ok thanks!!