Outlook VBA - open Excel, read in two fields, send email with these, close excel 
Author Message
 Outlook VBA - open Excel, read in two fields, send email with these, close excel


I'm using Outlook 2000.
I have an excel document that looks like this

subject1    company1
subject2    company2
subject3    company3

And I want to create an email for each of these three sets with the
first column date going in the subject field and the second column
data going in the body.

Basically all I need to do to get it working is
Open Excel (from outlook)
open a file
make this active
find the value of A1 (and use Excel's Format function)
find the value of B1 (same as above but less)
Pass these into another sub.
Close the excel document
Quit Excel.

Below I have Two sub's.  The first works fine when I pass in the two
pieces of info.  It just creates an email with some stuff in it and
sends it.
The second which gets the data from the excel and passes them into the
first doesn't work.

Public Sub Trafficfigures(currentCoyId As String, CurrentCompanyName
As String)

    Dim startNewOutlookApp As New Outlook.Application
    Dim newEmail As Outlook.MailItem
    Set newEmail = startNewOutlookApp.CreateItem(olMailItem)
    With newEmail

        .Subject = currentCoyId
        .Body = "Regarding" & CurrentCompanyName & vbCrLf & vbCrLf
        .Body = .Body & "Thanks, Phil"
        .Attachments.Add "D:/cofund/attach.xls"
    End With

End Sub

'This sub should open excel and the file emaillist.xls.
'It should then loop through the first column and while there is data
there it
'should read in the A and B columns for the row its on and call the
above Sub
'passing these two values in.

Sub LoopThroughList()
    Dim moreEmailsToSend As Boolean
    Dim currentRowI As Variant
    Dim currentRowS As String
    Dim currentCoyId As String
    Dim CurrentCompanyName As String
    moreEmailsToSend = True
    currentRowI = 1
    Set ExcelApp = CreateObject("Excel.Sheet")
    ExcelApp.Application.Workbooks.Open "D:/cofund/emaillist.xls"
    While moreEmailsToSend
        currentRowS = currentRowI
        currentCoyId =
Format(ExcelApp.Application.Worksheets(1).Range("A" +
currentRowS).Value, "00000")
        CurrentCompanyName =
ExcelApp.Application.Worksheets(1).Range("B" + currentRowS).Value
        moreCoyIds = False
        currentCoyId = ""
        If Not (currentCoyId = "") Then
            moreEmailsToSend = True
            Trafficfigures currentCoyId, CurrentCompanyName
        End If
        currentRowI = currentRowI + 1
End Sub

This second sub comes from something I created in Excel and I know the
looping part works fine.
When I run this in outlook 2000 it says it can't find the
emaillist.xls file.
It is however there and I can send it using the first sub as an
attachment so it can find it.
I've tried adding FileName:= which I use in excel but that doesn't
I have run this in Outlook XP(2002)(not what i'm designing for) and it
will do all this but won't close Excel, which uses 60% of the CPU, and
when I shut it down the macro says there was an error in the first
ExcelApp.Application.Worksheets(1).Range line.  Though the messages do
get sent.

Any help would be greatly appreciated.

Many thanks,

Philip Hanna.

Tue, 21 Sep 2004 20:47:09 GMT  
 [ 1 post ] 

 Relevant Pages 

1. having excel send an outlook email message when a field is changed

2. VBA in Excel, about two XLA in Excel

3. Sending Email from Excel using Outlook Express

4. Sending Email from Excel via Outlook Express

5. Sending Email from excel using vb or vba

6. need help: Open Excel, run macro, save file, close Excel

7. How close excel in outlook vba without dialog confirmation

8. macro that opens an excel attachment and then sends it to an email address

9. Excel VBA workbook app hangs on open/close

10. Reading Excel files without opening excel

11. Excel - VBA - Retreive file without opening them in Excel

12. Reading user-defined Outlook-fields from Excel


Powered by phpBB® Forum Software