Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2016
    Posts
    2

    Unanswered: Block If with out End If

    Hi Im having trouble with this. basically for the main body of the email I want it to change depending on if its a Invoice of Quotation.

    I'm getting an error when I try to run this.

    can any one help please cheers

    Code:
    Sub EmailClient()
      Dim IsCreated As Boolean
      Dim PdfFile As String, Title As String
      Dim OutlApp As Object
     
      ' Not sure for what the Title is
      Title = Range("A1")
     
      ' Define PDF filename
      Title = Range("R8").Value
      PdfFile = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & Title & ".pdf"
     
     
      ' Export activesheet as PDF
      With ActiveSheet
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
      End With
     
      ' Use already open Outlook if possible
      On Error Resume Next
      Set OutlApp = GetObject(, "Outlook.Application")
      If Err Then
        Set OutlApp = CreateObject("Outlook.Application")
        IsCreated = True
      End If
      OutlApp.Visible = True
      On Error GoTo 0
     
      ' Prepare e-mail with PDF attachment
      With OutlApp.CreateItem(0)
       
        ' Prepare e-mail
        .Subject = Range("S12").Value
        .To = Range("C13").Value ' <-- Put email of the recipient here
        .CC = "" ' <-- Put email of 'copy to' recipient here
        
        If R9 = Invoice Then
        
        .Body = "Hi " & Range("S11").Value & "," & vbLf & vbLf _
              & "Please find attached your invoice for the electrical works carried out, thank you for the opportunity to carry out these works." & vbLf & vbLf _
              & "Kind Regards,"
        
        ElseIf R9 = Quotation Then
         .Body = "Hi " & Range("S11").Value & "," & vbLf & vbLf _
              & "Quotation." & vbLf & vbLf _
              & "Kind Regards,"
                  .Attachments.Add PdfFile
       End
        ' Try to send
        Application.Visible = True
        .Display
      End
     
      ' Quit Outlook if it was not already open
      If IsCreated Then OutlApp.Quit
     
      ' Release the memory of object variable
      Set OutlApp = Nothing
     
    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Well the error message is telling you that the parser cannot find an end if closing off an if statement. That also includes else if.
    So go through each if statement and make certain there is a closing end if. The only time you dont need to use end if is if you use the frowned on but legal one line statement
    Code:
     if <boolean expression> then <do something>
    . Vba zealots would probably insist that you use the conventional syntax
    Code:
    If <boolean expression> then
      <do sonething>
    End if
    The error applies to other block statement structures such as while and do while.

    Having a quick skim through your code i'm uspicious of the ' else if R9'. Youve used code indentation which goes a large way to identjfy these glitches, so the fact youve use end, as opposed to end if AND the end statement is not aligned raises warning flags to me

    Im also suspicious of tge commentated out end after the first while statement. When writing long winded block constructs i often comment them eg
    Code:
    If usergroup = 'developer' then 'special case show debug message
      Debug.print.....
    Elseif userid = 'blah'
    ....
    Endif ' end usergroup block
    
    ' or sometime i repeat the if statement as a comment
    End if 'if usrrgroup ='developer'
    ...saves a shed load of time in code review

    I'd recommend that you stick to a maximum line length so code doesnt spill over several lines.. this may not be a problem in the vba editor but one of the reasons is when posting in forums its messy..
    Code:
    .Body  = 'a long winded piece of text' & vbcrlf _
           & 'another line of text that spans more than,' _
           & ' say 80 characters' _
           & ' still the same line droning on.' _
    ....
    The vba parser doesnt care about such nicities, you could stick all in one line or one word per line
    Theres nothing legal about this, its about style. Trying to make code look easier to read. So continuations of the line of code appear aligned under the first token to the right of the = sign. And code diesnt overflow. I find if code is formed inthis manner its an easy patterning approach that nakes it easier to visualise code blocks. Makes it easier for tired eyes to see the wood from the trees

    In terms of style note i use the &, string concatenation symbol, &, at the start of a vba line, if a space is needed its the first character of that vba line
    Last edited by healdem; 01-16-16 at 03:38.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2016
    Posts
    2

    Cheers

    Quote Originally Posted by healdem View Post
    Well the error message is telling you that the parser cannot find an end if closing off an if statement. That also includes else if.
    So go through each if statement and make certain there is a closing end if. The only time you dont need to use end if is if you use the frowned on but legal one line statement
    Code:
     if <boolean expression> then <do something>
    . Vba zealots would probably insist that you use the conventional syntax
    Code:
    If <boolean expression> then
      <do sonething>
    End if
    The error applies to other block statement structures such as while and do while.

    Having a quick skim through your code i'm uspicious of the ' else if R9'. Youve used code indentation which goes a large way to identjfy these glitches, so the fact youve use end, as opposed to end if AND the end statement is not aligned raises warning flags to me

    Im also suspicious of tge commentated out end after the first while statement. When writing long winded block constructs i often comment them eg
    Code:
    If usergroup = 'developer' then 'special case show debug message
      Debug.print.....
    Elseif userid = 'blah'
    ....
    Endif ' end usergroup block
    
    ' or sometime i repeat the if statement as a comment
    End if 'if usrrgroup ='developer'
    ...saves a shed load of time in code review

    I'd recommend that you stick to a maximum line length so code doesnt spill over several lines.. this may not be a problem in the vba editor but one of the reasons is when posting in forums its messy..
    Code:
    .Body  = 'a long winded piece of text' & vbcrlf _
           & 'another line of text that spans more than,' _
           & ' say 80 characters' _
           & ' still the same line droning on.' _
    ....
    The vba parser doesnt care about such nicities, you could stick all in one line or one word per line
    Theres nothing legal about this, its about style. Trying to make code look easier to read. So continuations of the line of code appear aligned under the first token to the right of the = sign. And code diesnt overflow. I find if code is formed inthis manner its an easy patterning approach that nakes it easier to visualise code blocks. Makes it easier for tired eyes to see the wood from the trees

    In terms of style note i use the &, string concatenation symbol, &, at the start of a vba line, if a space is needed its the first character of that vba line

    Cheers I managed to figure it out in the end, must admit I was tired when I did this lol!! Thank you for the advice and help

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •