Finding what is different from VB(MSVisualStudio 2005) and VBA(Excel)

Question

I found code online for something I wanted to do. As usual, I fired it up in Visual Studio and it works no problem.

The problem occurs in that, when I try to port it over to Excel, it ceases to work. As I understand, VBA is a watered down version of VB. (Based on reading this article: Difference between Visual Basic 6.0 and VBA)

Therefore, how to I find out what is lost between going between the two programming environments?

To give a bit more detail: I wrote a program in Visual Studio that sends me an email when I press a button. I then tried to port it into Excel as a Macro, but that didn't work.

EDIT: Adeed additional problem information

Here is what I have in Visual Studio Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    CDO_Mail_Small_Text()
End Sub

Sub CDO_Mail_Small_Text()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim Flds As Object

    iMsg = CreateObject("CDO.Message")
    iConf = CreateObject("CDO.Configuration")

    iConf.Load(-1)    ' CDO Source Defaults
    Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
                               = "morgan"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Update()
    End With

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    With iMsg
        .Configuration = iConf
        .To = "Jeremiah.Tantongco@Powerex.com"
        .CC = ""
        .BCC = ""
        .From = """Ron"" <ron@something.nl>"
        .Subject = "Important message"
        .TextBody = strbody
        .Send()
    End With

End Sub

End Class

Here is what I have running in Excel:

Sub Button1_Click()
    CDO_Mail_Small_Text
End Sub


Sub CDO_Mail_Small_Text()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim Flds As Object

iMsg = CreateObject("CDO.Message")
iConf = CreateObject("CDO.Configuration")

iConf.Load (-1)  
Flds = iConf.Fields
With Flds
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
                               = "morgan"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    .Update
End With

strbody = "Hi there" & vbNewLine & vbNewLine & _
            "This is line 1" & vbNewLine & _
            "This is line 2" & vbNewLine & _
            "This is line 3" & vbNewLine & _
            "This is line 4"

With iMsg
    .Configuration = iConf
    .To = "Jeremiah.Tantongco@Powerex.com"
    .CC = ""
    .BCC = ""
    .From = """Ron"" <ron@something.nl>"
    .Subject = "Important message"
    .TextBody = strbody
    .Send
End With

End Sub

The not working is a: "Run-time error '91': Object variable or With block variable not set"

When I debug it takes me to the following line: "iMsg = CreateObject("CDO.Message")"

Cheers, -Jeremiah Tantongco

Provide code or at the VERY least the error when it "ceases to work". — Dark Falcon, Oct 16 '09 at 23:58

score 4 · Answer 1 · answered Oct 17 '09 at 00:20

VBA is a compile-on-the-fly version of Classic VB. I always thought of it as sorta halfway between Full VB 6.0 and vb script. The key is you only have access to the basic VB 6.0 libraries and other COM libraries. Because many good com libraries are almost always available (like Scripting, ADO 2.6, the Office libraries like Excel and Word, etc.) this was actually very powerful.

However this is not .NET, and you have no access to .NET libraries whatsoever. When you say Visual Studio, do you mean Visual Studio 6.0? If you're copying code from VS.NET to Excel, that has no chance of working. But if you're copying code from VS6 (or earlier) to Excel VBA, you should be able to get that working. You probably just need to reference a library you were referencing in VS. We would need more information and of course the error.

score 1 · Accepted Answer · answered Oct 19 '09 at 18:28

1

In VB6/VBA you need to use the SET statement when working with objects

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

answered Oct 19 '09 at 18:28

DJ.

16,045
3
42
46

Thanks. It works now! I guess there are subtle differences in syntax. My reading lead me to believe syntax was identical! – Zigu Oct 19 '09 at 18:41

score 0 · Answer 3 · answered Oct 17 '09 at 22:41

0

Can't attest to the differences in code, but the following link has great examples of sending email in VBA. Might help you figure out what is going wrong.

http://www.rondebruin.nl/sendmail.htm

answered Oct 17 '09 at 22:41

guitarthrower

5,624
3
29
37

Finding what is different from VB(MSVisualStudio 2005) and VBA(Excel)

3 Answers3