Type Mismatch VBA cant be found

0
votes

I have Finally got some working code but Im getting a "type mismatch" error once the Module2 function finishes and I have no idea why.

If I step through it, it steps through "End function" on module2 then I get a Type Mismatch, but it does send the email. Any help would be great

This VBA code is in 3 parts.

1 Sub

  Sub Workbook_open()

  Call Module1.GetData

  End Sub

2 Module 1

  Public EmailAddress As String
  Public CompanyNumber As String
  Public Name As String
  Public GroupComp As String

  Function GetData()

  Dim LastRow As String
  Dim rng As Range


  LastRow = Cells(Rows.Count, "K").End(xlUp).Row

  For Each rng In Range("K2:K" + LastRow)

       If Not rng.Value = vbNullString Then
           Select Case rng.Value
               Case 1
                  Case Is = "True"
                    Let EmailAddress = ActiveCell.Offset(0, -5).Value
                    Let CompanyNumber = ActiveCell.Offset(0, -9).Value
                    Let Name = ActiveCell.Offset(0, -8).Value
                    Let GroupComp = ActiveCell.Offset(0, -7).Value
                    Call Module2.Email(EmailAddress, CompanyNumber, Name, GroupsComp)
               Case 2
                  Case Is = "False"
           End Select

        End If
    Next
   End Function

3 Module 2

      Function Email()
     'MsgBox (EmailAddress)
     Set objMessage = CreateObject("CDO.Message")
     objMessage.Subject = "Stuffl " & (GroupComp)
     objMessage.From = "Department Name([email protected])"
     objMessage.Cc = "Department Name([email protected])"
     objMessage.To = (EmailAddress)
     MsgBox (EmailAddress)
     objMessage.TextBody = "TEST"

      objMessage.Configuration.Fields.Item _
      ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

      objMessage.Configuration.Fields.Item _
      ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "x.x.x.x"

      objMessage.Configuration.Fields.Item _
      ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

      objMessage.Configuration.Fields.Update

      objMessage.Send

    End Function
2
vbaexcel
In Module1.GetData you are calling Module2.Email with 4 arguments, but at its definition there are no arguments? Also, why use Function and not Sub instead? The functions you define will return Variant objects, is that necessary? – Ioannis
Function Email() why its a function if it does not return anything ? Check out this link – Santosh

2 Answers

1
votes

Change the Function Email() into 

Function Email(emailaddress As String, companynumber As String, name As String, groupscomp As String)

You are not actually calling the Mail function , that's the problem i think

1
votes

"i dont understand why I had to specify the variables again in the mail"

Whenever you pass values to a Sub or Function, you have to define that Sub or Function such that it is expecting to have values passed to it. So this won't work:

Sub Foo()
    Dim i as Integer
    i = 5
    Call Bar(i)
End Sub

Sub Bar()
    i = i + 2
End Sub

Because Bar() isn't expecting to have anything passed to it. This will work:

Sub Foo()
    Dim i as Integer
    i = 5
    Call Bar(i)
End Sub

Sub Bar(i as Integer)
    i = i + 2
End Sub

Because you have now told Bar to expect an integer to be passed to it.

Hope that helped.