Compile Error - Communications

[harrybillyard9]

Hi,

i have tried to produce a system of communications using VBA, i have followed videos online however i am at a standstill and would appreciate any assistance

I keep hitting a Compile Error - Invalid Outside Procedure

My code is as follows:

Option Explicit

Dim communicationno As Long
Dim reff As String
Dim communicationtype As String
Dim custname As String
Dim projectno As String
Dim dt_issue As Date
Dim term As Byte
Dim nextrec As Range
Dim path As String
Dim fname As String

reff = Range("C6")
communicationtype = Range("B21")
custname = Range("B13")
projectno = Range("C5")
dt_issue = Range("C7")
term = Range("C8")

Set nextrec = Sheet3.Range("A1048576").End(xlUp).Offset(1, 0)

nextrec = communicationref
nextrec.Offset(0, 1) = communicationno
nextrec.Offset(0, 2) = communicationtype
nextrec.Offset(0, 3) = communicationref
nextrec.Offset(0, 4) = custname
nextrec.Offset(0, 5) = dt_issue
nextrec.Offset(0, 6) = term


End Sub

Sub SaveInv()

Dim shp As Shape

path = "" '\\corpfsv02\Renewals Project\1.Capital Projects\BE\BE025 - Monkseaton Station\4 Financial & Commercial (Post-Award SG4-7)\2 Main Contractor\3 Communications\2 Contract Communications\NEC4 Comms Template\HB\Project Communications\
communicationref = Range("C6")
dt_issue = Range("C7")
term = Range("C8")
custname = Range("B13")
fname = Range("C6")

Application.DisplayAlerts = False

Sheet1.Copy

For Each shp In ActiveSheet.Shapes
    If shp.Type <> msoPicture Then shp.Delete 'This line is modified so that is doesn't delete the logo
Next shp
  
    With ActiveWorkbook
        .Sheets(1).Name = "communicationrefice"
        .SaveAs Filename:=path & fname, FileFormat:=51
        .Close
    End With

Application.DisplayAlerts = True

Set nextrec = Sheet3.Range("A1048576").End(xlUp).Offset(1, 0)

nextrec.Offset(0, 1) = communicationno
nextrec.Offset(0, 2) = communicationtype
nextrec.Offset(0, 3) = communicationref
nextrec.Offset(0, 4) = custname
nextrec.Offset(0, 5) = dt_issue
nextrec.Offset(0, 6) = term

Sheet3.Hyperlinks.Add anchor:=nextrec.Offset(0, 7), Address:=path & fname & ".xlsx"
End Sub

Sub SaveAspdf()

path = "" '\\corpfsv02\Renewals Project\1.Capital Projects\BE\BE025 - Monkseaton Station\4 Financial & Commercial (Post-Award SG4-7)\2 Main Contractor\3 Communications\2 Contract Communications\NEC4 Comms Template\HB\Project Communications\
communicationref = Range("C6")
custname = Range("B13")
fname = communicationref & " - " & custname

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
IgnorePrintAreas:=False, _
Filename:=path & fname

Set nextrec = Sheet3.Range("A1048576").End(xlUp).Offset(1, 0)
nextrec = communicationref
nextrec.Offset(0, 1) = communicationno
nextrec.Offset(0, 2) = communicationtype
nextrec.Offset(0, 3) = communicationref
nextrec.Offset(0, 4) = custname
nextrec.Offset(0, 5) = dt_issue
nextrec.Offset(0, 6) = term

Sheet3.Hyperlinks.Add anchor:=nextrec.Offset(0, 6), Address:=path & fname & ".pdf"

End Sub

Sub StartNewcommunicationrefice()

communicationno = Range("C3")

Range("B10, C4:D4, B19:G35").ClearContents

MsgBox "Your next communicationrefice number is " & invno + 1

Range("C3") = invno + 1
Range("B10").Select

ThisWorkbook.Save
End Sub


Sub EmailasPDF()

Dim EApp As Object
Set EApp = CreateObject("Outlook.Application")

Dim EItem As Object

path = "" ''\\corpfsv02\Renewals Project\1.Capital Projects\BE\BE025 - Monkseaton Station\4 Financial & Commercial (Post-Award SG4-7)\2 Main Contractor\3 Communications\2 Contract Communications\NEC4 Comms Template\HB\Project Communications\
communicationref = Range("C6")
custname = Range("B13")
fname = communicationref & " - " & custname
dt_issue = Range("C7")
term = Range("C8")

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
IgnorePrintAreas:=False, _
Filename:=path & fname

Set nextrec = Sheet3.Range("A1048576").End(xlUp).Offset(1, 0)

nextrec = communicationref
nextrec.Offset(0, 1) = communicationno
nextrec.Offset(0, 2) = communicationtype
nextrec.Offset(0, 3) = communicationref
nextrec.Offset(0, 4) = custname
nextrec.Offset(0, 5) = dt_issue
nextrec.Offset(0, 6) = term

Sheet3.Hyperlinks.Add anchor:=nextrec.Offset(0, 6), Address:=path & fname & ".pdf"

    Set EItem = EApp.CreateItem(0)
          
    With EItem
  
        .To = Range("B16")
  
        .Subject = "Project Communication - : " & Range("C6")
  
        .Body = "Please find project communication attached."
  
        .Attachments.Add (path & fname & ".pdf")
  
        .Display
      
    End With
Exit Sub



End Sub





Sub vba_autofit()

Range("B22").EntireRow.AutoFit
Range("B22").EntireColumn.AutoFit


End Sub

 

[Georgiboy]

reff is defined as a string type but Range("C6") is a range.

Try adding .Value into the mix as below, this will take the value from the cell instead.

Range("C6").Value

 

[harrybillyard9]

reff is defined as a string type but Range("C6") is a range.

Try adding .Value into the mix as below, this will take the value from the cell instead.

Range("C6").Value

Hi, thanks for your speedy reply

the actual text in cell C6 is defined by using the GetFirstLetters VBA which currently provides a unique reference for the sheet based on the data in it.

As this is not a value would that change this?

Thanks again

 

[Georgiboy]

I am not sure i follow you, what is reff to hold?

What does reff contain if you add the '.Value' on the end?

 

[harrybillyard9]

I am not sure i follow you, what is reff to hold?

What does reff contain if you add the '.Value' on the end?

Hi,

I still get the same error,

This is what is currently in Cell C6

I must apologise i am not very technical

Thanks

 

[Georgiboy]

Just noticed, your first macro does not have a Sub line:
Before this line at the top of the code:

Dim communicationno As Long

You would need something like:

Sub something()