Outlook x Excel - Can´t make it work

[AlexandreSRS]

Hello,

I'm trying to get the outlook running a code that get some data from a excel workbook. I entered the code bellow but it keeps stop working as soon as it gets to the "ExWbk =ExApp.Workbooks". I assume that I'm not declaring it right, but I coudn´t find anything on the matter. I really don't need to open the Plan Aux.xlsm workbook (It already is)

Thnx!!!

Sub Recebimento_Aut_TQF(mymail As Outlook.MailItem)



Dim Caminho, strSubject, Barco, NomeElaborador, RefPE, EmailElaborador, EmailCTDC, TQFTeste As String
Dim LinhaTQF, numlin, i, LinhaElaborador, LinhaCT, LinhaDC, DataUltAtual, TQFtotal, LinTQFtotal As Double


Dim ExApp As Excel.Application
Dim ExWbk As Workbook
Set ExApp = New Excel.Application
Set ExWbk = ExApp.Workbooks.Open("C:\Users\ss7b1365\Desktop\Outlook\Plan Aux.xlsx")
ExApp.Visible = False

[Kenneth Hobs]

Welcome to the forum!

Are you sure that the file exists? You can use Dir() to determine if it exists.

Since you say that it exists and open already, you need to use GetObject() rather than new instance of Excel. Excel can not open a file that that is already open.

I guess you know that each variable needs a type? Set the type for one variable at the end of the Dim line only sets that one. Others would be type Variant if not specifically set.

[gmayor]

I would suggest also that you change the code to the following and the (visibility of the workbook to True while testing)

Dim ExApp As Object
Dim ExWbk As Object

    On Error Resume Next
    Set ExApp = GetObject(, "Excel.Application")
    If Err <> 0 Then
        Set ExApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0

    Set ExWbk = ExApp.Workbooks.Open("C:\Users\ss7b1365\Desktop\Outlook\Plan Aux.xlsx")
    ExApp.Visible = True 'False

[AlexandreSRS]

Guys, thanks a lot for the answers, it did the trick. Just one last thing that is not working. The main reason for my code is to get a specific part of the subject from a new email, compare and return some information from an excel workbook, then forward this email with some informations. The code do everything that I wrote except for the lines bellow . All the variables are loaded with content, but when I put to display the forward message, the subject,recipients and body comes blank.

One last thing that I couldn't make it work is putting CC to this forward email. When I try "mymail.Forward.CC" it shows a message saying it is not possible.

Can you guys help me out?

'************************************************************************** ************

mymail.Forward.Subject = "Resposta da " & NumeroTQF & " referente ao projeto " & NumeroPE & "."

mymail.Forward.Body = "Atenção, existem " & TQFRest & "TQFs pedentes para o projeto " & NumeroPE

mymail.Forward.Recipients.Add (EmailElab)

'mymail.Forward.CC (EmailCT & ";" & EmailDocC)

mymail.Send

'************************************************************************** ************

[gmayor]

Your message gives no indication of what is being forwarded or what the various variables refer to, however looking at what you have posted, I don't see any reason to forward the message at all, as your code overwrites the message body of the message you are forwarding and you are changing the subject. If that is what is intended simply create a new message:

Dim myMail As MailItem

    Set myMail = CreateItem(olMailItem)
    With myMail
        .Subject = "Resposta da " & NumeroTQF & " referente ao projeto " & NumeroPE & "."
        .Body = "Atencao, existem " & TQFRest & "TQFs pedentes para o projeto " & NumeroPE
        .To = EmailElab
        .CC = EmailCT & ";" & EmailDocC
        .Display 'This line is only for testing
        '.Send
    End With

If you want to forward a message you need to refer to that message you are forwarding and add the response text to the start of the forwarded message e.g. as follows where olMsg is the message you are forwarding and myMail is the forwarded version of that message. oRng is the body of the message and here .Display is required even when you restore .Send.

As I have no idea what is in the variables I can only assume they are appropriate to their uses.

Dim olMsg As MailItem
Dim myMail As MailItem
Dim olInsp As Outlook.Inspector
Dim wdDoc As Object
Dim oRng As Object

    Set olMsg = ActiveExplorer.Selection.Item(1)
    Set myMail = olMsg.Forward
    With myMail
        .Subject = "Resposta da " & NumeroTQF & " referente ao projeto " & NumeroPE & "."
        .To = olMsg.SenderEmailAddress & ";" & EmailElab
        .CC = EmailCT & ";" & EmailDocC
        Set olInsp = .GetInspector
        Set wdDoc = olInsp.WordEditor
        Set oRng = wdDoc.Range
        .Display 'This line is required!
        oRng.collapse 1
        oRng.Text = "Atencao, existem " & TQFRest & "TQFs pedentes para o projeto " & NumeroPE
        '.Send
    End With

[AlexandreSRS]

Sorry for my late replay, guys!

Thank you very much, it worked as I needed!