1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Error 400 in Excel Macro

Discussion in 'Business Applications' started by Amapola, May 4, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Amapola

    Amapola Thread Starter

    Joined:
    May 4, 2010
    Messages:
    26
    Hi there,
    I've cobbled below code together (I'm game but probably not even a pedestrian in VBA, maybe more of a snail) to take some information from an Excel sheet and put it into an e-mail. I also want the active workbook attached to the e-mail and if possible another file with the same name but in pdf (haven't looked at that yet).

    The ErrorCatch says it's an object or applications driven error.

    I'm not sure about this line: .Attachments.Add ActiveWorkbook.FullName but really have no clue where the problem is.

    Any help would be greatly appreciated. Thanks.

    Christine

    Function GetBoiler(ByVal sFile As String) As String
    'Dick Kusleika
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
    End Function

    Sub Mail_small_Text_Outlook()
    'Working in Office 2000-2010

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim SigString As String
    Dim Signature As String

    On Error GoTo Errorcatch


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = "Hi Leon" & vbNewLine & vbNewLine & _
    "" & vbNewLine & _
    "Attached invoice for " & Cells(C14) & ".Can you please approve for payment?" & vbNewLine & _
    "Supplier: " & Cells(E43) & vbNewLine & _
    "Invoice Number:" & Cells(L4) & vbNewLine & _
    "Amount: " & Cells(O37) & vbNewLine & _
    "Purpose:" & Cells(C14) & vbNewLine & _
    "" & vbNewLine & _
    "Thanks." & vbNewLine & _
    "" & vbNewLine & _



    SigString = "D:\Documents and Settings\305015724\Application Data\Microsoft\Signatures\christine.txt"

    If Dir(SigString) <> "" Then
    Signature = GetBoiler(SigString)
    Else
    Signature = ""
    End If

    On Error Resume Next
    With OutMail
    .To = "[email protected]"
    .CC = ""
    .BCC = ""
    .Subject = Cells(C14) & " - " & Cells(E43) & "- Your approval required"
    .Body = strbody
    .Attachments.Add ActiveWorkbook.FullName
    'You can add a file like this
    '.Attachments.Add ("C:\test.txt")
    .Display 'or use .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

    Exit Sub

    Errorcatch:
    MsgBox Err.Description


    End Sub
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hello Amapola, welcome to the board,

    Have you tried stepping through the code?
    Open VBA project and click on the first line of executable code
    The process will halt there and then using the F8 button you can step throuhg each line and see where problem is.

    Let us know

    If you have sheet with non sensitive data you could attach a asample, that way it's easier to check.

    Error 400 is a 'bad request' or 'incorrect syntax'
    You could check the link to the sigfile of maybe the attachment c:\.....txt
    If must be some place here
     
  3. Amapola

    Amapola Thread Starter

    Joined:
    May 4, 2010
    Messages:
    26
    Hi Hans

    Thanks for you reply.

    Stepping through with F8, I get yellow highlights at the first line, the ErrorCatch, Set, strbody and the MsgBox. I only put in the ErrorCatch and MsgBox after I tried it and came up with the error and the Set and strbody bit are straight out of the code I copied.

    I have attached the Excel sheet ... Thanks.

    Christine
     

    Attached Files:

  4. Amapola

    Amapola Thread Starter

    Joined:
    May 4, 2010
    Messages:
    26
    Hi there

    I can generate the e-mail now but not with the cell contents inserted into the subject and body of the mail.

    From what I have found on the internet, this should work but it doesn't:
    .Subject = Cells(C, 14) & " - " & Cells(E, 43) & "- Your approval required"

    If I take the Cell References out, it works fine. I've seen cell references used like this in code on the internet so I wonder where the problem is.

    I'm generating an HTML mail now.

    Any idea?

    Thanks, Christine
     
  5. Amapola

    Amapola Thread Starter

    Joined:
    May 4, 2010
    Messages:
    26
    I got it!

    The correct way to input the cells was [A1].Value and that works a dream.

    I'm still looking for a way to attach a pdf document with the same name as the Excel sheet. If that is possible.

    Thanks, Christine
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Just to be sure, I see in the last post that you say "I got it!"
    Does that mean that you've solved it?
    I don't need to look at your example anymore?
     
  7. Amapola

    Amapola Thread Starter

    Joined:
    May 4, 2010
    Messages:
    26
    Hi Hans

    Yes, generating the e-mail and putting all the information in works.

    I'm still trying to also attach a pdf with the same name as the Excel sheet and in theory I know what I would need to do. In practice, I don't know how to do it. If you have a solution for that, I would be grateful. Here goes:

    I would need to get the name of the Excel sheet which would be something.xls. I will have set values for the path and a set value ".pdf". Then I would need to tell it to remove the .xls from the filename and replace it with the .pdf and assembly that name with the path. In the code to attach the file, I then would have to refer to the value created.

    I've found code to output the filename to a cell but I don't really need it there. It can stay within the code. I'm sure it's possible so I'll just keep tinkering until I got it.

    Thanks for your help to set me on the right track.

    Christine
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi Christine,
    activeworkbook.name returns the nam of the Excel file including the extension

    Activeworkbook.Fullpath returns the full path C:\Somewhere\My Documents\Excel

    Maybe you can do something with this?

    Hans
     
  9. Amapola

    Amapola Thread Starter

    Joined:
    May 4, 2010
    Messages:
    26
    Hi Hans

    I found the following code which should return the full path of the file and change the File Extension from .xls to .pdf.

    Function GetFullFile() As String
    Dim strPath As String
    strPath = ActiveWorkbook.FullPath
    GetFullFile = Replace(strPath, ".xls", ".pdf")
    End Function

    I then try to attach that file with
    .Attachments.Add GetFullFile

    But it doesn't work. GetFullFile is a string but I can't find anything that says how to use a string for this purpose or that it wouldn't work.

    Any ideas?

    Thanks so much.
    Christine
     
  10. Amapola

    Amapola Thread Starter

    Joined:
    May 4, 2010
    Messages:
    26
    Hi Hans

    These is the new code and it works:

    To change the file extension:
    Function GetFullFile() As String
    Dim strFPath As String
    strFName = ActiveWorkbook.FullName
    GetFullFile = Replace(strFName, ".xls", ".pdf")
    End Function

    To add attachment:
    .Attachments.Add (GetFullFile)

    All perfect now.

    Never give up!

    Christine
     
  11. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Your 'solution' does not convert the xls, all you did was rename the file but an xls file is not a pdf file.
    Please explain what your real intention is.
     
  12. Amapola

    Amapola Thread Starter

    Joined:
    May 4, 2010
    Messages:
    26
    Ah, no, I didn't mean to convert it. I have an excel sheet and a pdf, both with the same name and I want to attach both. It's for accounting purposes. The excel sheet codes the sheet (what it is for, who pays, etc.) and the pdf is the scanned invoice.

    So all I'm after is having the pdf automatically attached.

    Christine
     
  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    then you will have to create 2 variables:

    myExcel = ActiveWorkbook.FullName
    myPdf = Replace(strFName, ".xls", ".pdf")
    and then
    .Attachments.Add (myExcel)
    .Attachments.Add (myPDF)

    You can add as many .Attachments.Add () as you have attachments.
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/920972

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice