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: http://stackoverflow.com/questions/993300/difference-between-visual-basic-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 = "[email protected]"
        .CC = ""
        .BCC = ""
        .From = """Ron"" <[email protected]>"
        .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 = "[email protected]"
    .CC = ""
    .BCC = ""
    .From = """Ron"" <[email protected]>"
    .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

Answer 1

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.

Answer 2

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

Answer 3

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

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