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
what is a drop-in?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.
ok thanks!!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.