By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 2,154 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

Return value of FN procedure dim'd as Variant cannot be assigned an Array value

P: n/a
MLH

The following procedures found at
http://ffdba.com/downloads/Send_Mail...ok_Express.htm
are meant to work together in harmony to effect eMail sends via OE.
The last procedure (FN SplitB) fails on its last line which is meant
to assign the value of SplitB (a variant) the value of aSplit (an
array dim'd as variant). That fails due to Type Mismatch. Anyone
know how to get around this?

Private Type MapiRecip
Reserved As Long
RecipClass As Long
Name As String
Address As String
EIDSize As Long
EntryID As Long
End Type

Private Type MAPIFileDesc
Reserved As Long
flags As Long
Position As Long
PathName As String
fileName As String
FileType As Long
End Type

Private Type MAPIMessage
Reserved As Long
Subject As String
NoteText As String
MessageType As String
DateReceived As String
ConversationID As String
Originator As Long
flags As Long
RecipCount As Long
Recipients As Long
FileCount As Long
Files As Long
End Type

Declare Function MAPISendMail _
Lib "c:\program files\outlook express\msoe.dll" ( _
ByVal Session As Long, _
ByVal UIParam As Long, _
Message As MAPIMessage, _
ByVal flags As Long, _
ByVal Reserved As Long) As Long

Public Sub SendMailWithOE(ByVal vSubject As String, _
ByVal vMessage As String, _
ByRef vRecipients As String, _
Optional ByVal vFiles As String)

Dim aFiles() As String
Dim aRecips() As String

Dim FilePaths() As MAPIFileDesc
Dim Recips() As MapiRecip
Dim Message As MAPIMessage

Dim z As Long

aFiles = Split(vFiles, ",")
ReDim FilePaths(LBound(aFiles) To UBound(aFiles))
For z = LBound(aFiles) To UBound(aFiles)
With FilePaths(z)
.Position = -1
.PathName = StrConv(aFiles(z), vbFromUnicode)
End With
Next z

aRecips = Split(vRecipients, ",")
ReDim Recips(LBound(aRecips) To UBound(aRecips))
For z = LBound(aRecips) To UBound(aRecips)
With Recips(z)
.RecipClass = 1
If InStr(aRecips(z), "@") <0 Then
.Address = StrConv(aRecips(z), vbFromUnicode)
Else
.Name = StrConv(aRecips(z), vbFromUnicode)
End If
End With
Next z

With Message
.FileCount = UBound(FilePaths) - LBound(FilePaths) + 1
.Files = VarPtr(FilePaths(LBound(FilePaths)))
.NoteText = vMessage
.RecipCount = UBound(Recips) - LBound(Recips) + 1
.Recipients = VarPtr(Recips(LBound(Recips)))
.Subject = vSubject
End With
MAPISendMail 0, 0, Message, 0, 0
End Sub

Private Sub Test_SendMailWithOE()
Dim aFiles() As Variant
Dim aRecips() As String
Dim Files As String
Dim Message As String
Dim Recipients As String
Dim Subject As String

' not required
Files = "C:\twacker.log"
Files = Files & "," & "C:\readme.txt"

' required
Recipients = "So*****@Some.Domain"

' not required
Recipients = Recipients & "," & "So*********@SomeOther.Domain"

Message = "Let me know if you get this, please."

Subject = "Test"

SendMailWithOE Subject, Message, Recipients, Files

End Sub

Public Function SplitB(ByVal SplitString As String, ByVal Delimiter As
String) As Variant
Dim Position As Long
Dim aSplit() As Variant
Dim Dimension As Long
Position = InStr(SplitString, Delimiter)
Do While Position <0
ReDim Preserve aSplit(Dimension)
aSplit(UBound(aSplit)) = Trim(Left(SplitString, Position - 1))
SplitString = Mid$(SplitString, Position + 1)
Dimension = Dimension + 1
Position = InStr(SplitString, Delimiter)
Loop
ReDim Preserve aSplit(Dimension)
aSplit(Dimension) = Trim(SplitString)
SplitB = aSplit
End Function
Nov 19 '06 #1
Share this Question
Share on Google+
11 Replies


P: n/a
SplitB returns a variant array.

It can be used as

Dim aV As Variant
aV = SplitB("Lyle R Fairfield", " ")
Debug.Print aV(0)
'Lyle

'or

Dim aV As Variant
Dim z As Long
For z = LBound(aV) To UBound(aV)
Debug.Print aV(z)
Next z
'Lyle
'R
'Fairfield

'using

SplitB("Lyle R Fairfield", " ")(0)
SplitB("Lyle R Fairfield", " ")(1)
SplitB("Lyle R Fairfield", " ")(2)

'for the above task works but is more work than we should ask VBA to
do.

Regardless I think it's no longer required for the SendMailwithOE code?

--
Lyle Fairfield
Nov 19 '06 #2

P: n/a
MLH
<snip>
The procedures at
http://www.ffdba.com/downloads/Send_...ok_Express.htm
still use it. Its a very handy set of procedures there. I'll try to
get it working. If I can, I'll post it here.

>
Regardless I think it's no longer required for the SendMailwithOE code?
Nov 19 '06 #3

P: n/a
MLH wrote:
The procedures at
http://www.ffdba.com/downloads/Send_...ok_Express.htm
still use it. Its a very handy set of procedures there. I'll try to
get it working. If I can, I'll post it here.
I wrote code to send mail from Access with Outlook Express years ago
as:
1. a recreation;
2. to demonstrate that those who say """IT""" can't be done in Access
are often wrong;
3. to show that exposed functions in a dll can often be used in VB
regardless of their being documented or intended to be used.

Later some people decided to use the code.

The original code had an error in one of the type declarations which
prevented it from sending attachments.

Later exactly the same code was created by non-English developers who
posted it on their sites (copyrighted) with different function names.
The presence of the identical error was a very strange coincidence. Who
woulda thunk it?

I have never been serious about this code as far as its actually being
useful. I use it to test things like sending varptrs to API functions.
So it often is in a won't work state, which is true right now.

But, if you have some distinct use for it let me know here in the
newsgroup and I will try to modify it so that it is clean simple and
works.

It won't be until Tuesday as I have appointments tomrrow. (unless I get
bored in the meantime).

Nov 19 '06 #4

P: n/a

Lyle Fairfield wrote:
MLH wrote:
The procedures at
http://www.ffdba.com/downloads/Send_...ok_Express.htm
still use it. Its a very handy set of procedures there. I'll try to
get it working. If I can, I'll post it here.

I wrote code to send mail from Access with Outlook Express years ago
as:
1. a recreation;
2. to demonstrate that those who say """IT""" can't be done in Access
are often wrong;
3. to show that exposed functions in a dll can often be used in VB
regardless of their being documented or intended to be used.

Later some people decided to use the code.

The original code had an error in one of the type declarations which
prevented it from sending attachments.

Later exactly the same code was created by non-English developers who
posted it on their sites (copyrighted) with different function names.
The presence of the identical error was a very strange coincidence. Who
woulda thunk it?

I have never been serious about this code as far as its actually being
useful. I use it to test things like sending varptrs to API functions.
So it often is in a won't work state, which is true right now.

But, if you have some distinct use for it let me know here in the
newsgroup and I will try to modify it so that it is clean simple and
works.

It won't be until Tuesday as I have appointments tomrrow. (unless I get
bored in the meantime).
Upon further review (after the Grey Cup game) it seems that what we are
talking about is sending an html formatted message with simple mapi
calls. At this time I do not know how to do that. The CDO works so well
that it seems pointless to investigate.

Nov 20 '06 #5

P: n/a
Hi MLH

There is a split command, so I'm not sure why you are doing it this way

I'd do it like this

Public Function SplitB(ByVal delimitedData As String, ByVal Delimiter
As
String) As Variant
Dim result() As string
result = split( delimitedData , delimiter )
SplitB = result
End Function

Keith

Nov 20 '06 #6

P: n/a
MLH
Not available in A97. The OP is not about
parsing a string. The title is "Return value of FN procedure dim'd as
Variant cannot be assigned an Array value"

Does anyone know a working method of assigning SplitB = aSplit?

This is aSplit ==Dim aSplit() As Variant ==an array of variants

SplitB is a function whose return value has been dim'd as Variant.

The line that's failing is trying to assign the value of aSplit to
SplitB. Anybody know a way to do that which works? Obviously,
one cannot simply make the assignment as Lyle tried. The code
fails due to mismatch. But I'm sure there's a way. I was thinking
about looping, but I don't believe that's the answer. I can see what
Lyle was trying to do. Conceptually, it was a good idea. But it ain't
workin'. I don't know why ==the following works...

Private Sub Command0_Click()
Dim A As Variant
Dim B(2) As String
B(0) = "qwer"
B(1) = "asdf"
B(2) = "zxcv"
A = B
Debug.Print A(0), A(1), A(2)

End Sub

but his code does not. I wonder why not? Seems like it should.
Nov 21 '06 #7

P: n/a
MLH
Am sure CDO is easy & full featured. But, no, this
has nothing to do with HTML. I snatched the code
from http://ffdba.com/ under the heading

Code I Have Used

and I was curious about the one line of code that
didn't work. Knowing that you'd written & used the
snippet prior to CDO, I'm sure you encountered the
same error I did in A97. Conceptually and at first
glance, it seems the last functional line in your SplitB
function should work. But Access doesn't think so
and I was wondering why? I mean, its not too
different from this example:

Private Sub Command0_Click()
Dim A As Variant
Dim B(2) As String
B(0) = "qwer"
B(1) = "asdf"
B(2) = "zxcv"
A = B
Debug.Print A(0), A(1), A(2)
End Sub

and this example works. So why doesn't your code?
Nov 21 '06 #8

P: n/a
MLH <CR**@NorthState.netwrote in news:hgv5m2hne62t245khkqbhn35tj7h63dto1@
4ax.com:
Am sure CDO is easy & full featured. But, no, this
has nothing to do with HTML. I snatched the code
from http://ffdba.com/ under the heading

Code I Have Used

and I was curious about the one line of code that
didn't work. Knowing that you'd written & used the
snippet prior to CDO, I'm sure you encountered the
same error I did in A97. Conceptually and at first
glance, it seems the last functional line in your SplitB
function should work. But Access doesn't think so
and I was wondering why? I mean, its not too
different from this example:

Private Sub Command0_Click()
Dim A As Variant
Dim B(2) As String
B(0) = "qwer"
B(1) = "asdf"
B(2) = "zxcv"
A = B
Debug.Print A(0), A(1), A(2)
End Sub

and this example works. So why doesn't your code?
My code has worked for a long time. Perhaps. if you posted your use of the
code, completely, we could help each other understand what the other is
talking about.
Nov 21 '06 #9

P: n/a
MLH
Lyle wrote:
>My code has worked for a long time. Perhaps. if you posted your use of the
code, completely, we could help each other understand what the other is
talking about.
MLH wrote:
Sure, no problem. I cut 'n pasted this from A97 module created to hold
only the declarations and 3 procedures shown below...

Option Compare Database
Option Explicit
'************************************************* ***********************************
' Retrieved from
http://ffdba.com/downloads/Send_Mail...ok_Express.htm
'************************************************* ***********************************
Private Type MapiRecip
Reserved As Long
RecipClass As Long
Name As String
Address As String
EIDSize As Long
EntryID As Long
End Type

Private Type MAPIFileDesc
Reserved As Long
flags As Long
Position As Long
PathName As String
fileName As String
FileType As Long
End Type

Private Type MAPIMessage
Reserved As Long
Subject As String
NoteText As String
MessageType As String
DateReceived As String
ConversationID As String
Originator As Long
flags As Long
RecipCount As Long
Recipients As Long
FileCount As Long
Files As Long
End Type

Declare Function MAPISendMail _
Lib "c:\program files\outlook express\msoe.dll" ( _
ByVal Session As Long, _
ByVal UIParam As Long, _
Message As MAPIMessage, _
ByVal flags As Long, _
ByVal Reserved As Long) As Long

Public Sub SendMailPlusWithOE(ByVal vSubject As String, _
ByVal vMessage As String, _
ByRef vRecipients As String, _
Optional ByVal vFiles As String)

Dim aFiles() As String
Dim aRecips() As String

Dim FilePaths() As MAPIFileDesc
Dim Recips() As MapiRecip
Dim Message As MAPIMessage

Dim z As Long

'THE FOLLOWING LINE WON'T COMPILE & I HAD TO REM IT OUT UNTIL I CAN
GET A FIX
' aFiles = SplitB(vFiles, ",") 'aFiles is an array. vFiles is a
string of quoted, comma-delimited filenames (incl full paths)
ReDim FilePaths(LBound(aFiles) To UBound(aFiles))
For z = LBound(aFiles) To UBound(aFiles)
With FilePaths(z)
.Position = -1
.PathName = StrConv(aFiles(z), vbFromUnicode)
End With
Next z

'THE FOLLOWING LINE WON'T COMPILE & I HAD TO REM IT OUT UNTIL I CAN
GET A FIX
' aRecips = SplitB(vRecipients, ",")
ReDim Recips(LBound(aRecips) To UBound(aRecips))
For z = LBound(aRecips) To UBound(aRecips)
With Recips(z)
.RecipClass = 1
If InStr(aRecips(z), "@") <0 Then
.Address = StrConv(aRecips(z), vbFromUnicode)
Else
.Name = StrConv(aRecips(z), vbFromUnicode)
End If
End With
Next z

With Message
.FileCount = UBound(FilePaths) - LBound(FilePaths) + 1
.Files = VarPtr(FilePaths(LBound(FilePaths)))
.NoteText = vMessage
.RecipCount = UBound(Recips) - LBound(Recips) + 1
.Recipients = VarPtr(Recips(LBound(Recips)))
.Subject = vSubject
End With
MAPISendMail 0, 0, Message, 0, 0
End Sub

Private Sub Test_SendMailPlusWithOE()
Dim aFiles() As Variant
Dim aRecips() As String
Dim Files As String
Dim Message As String
Dim Recipients As String
Dim Subject As String

' not required
Files = "C:\WS_FTP.LOG"
Files = Files & "," & "C:\TreeGuys.txt"

' required
Recipients = "cr**@northstate.net"

' not required
Recipients = Recipients & "," & "Ki***********@ComCast.net"

Message = "If you get this, please throw it away. It is a test."

Subject = "Test"

SendMailPlusWithOE Subject, Message, Recipients, Files

End Sub

Public Function SplitB(ByVal SplitString As String, ByVal Delimiter As
String) As Variant
Dim Position As Long, i As Integer
Dim aSplit() As Variant
Dim Dimension As Long
Position = InStr(SplitString, Delimiter)
Do While Position <0
ReDim Preserve aSplit(Dimension)
aSplit(UBound(aSplit)) = Trim(left(SplitString, Position - 1))
For i = 0 To UBound(aSplit): Debug.Print aSplit(i): Next i
SplitString = Mid$(SplitString, Position + 1)
Dimension = Dimension + 1
Position = InStr(SplitString, Delimiter)
Loop
ReDim Preserve aSplit(Dimension)
aSplit(Dimension) = Trim(SplitString)
SplitB = aSplit
End Function

Lyle, to date, my "use of the code, completely" has been limited
to calling Private Sub Test_SendMailPlusWithOE() furnished on the
site. When I do run it the Type MisMatch error is reported when last
functional line of code in the SplitB procedure is executed. Maybe
I didn't copy everything I was supposed to - I don't think so - but
let me know if that's the case.
Nov 21 '06 #10

P: n/a
The OE thing was rewritten for Access >=2000 and changed from using the
SplitB function to using the Split function which returns an array of
strings. In order for the SplitB function to return an array in Access
97 it must be dimmed as variant.

Initially these two:

Dim aFiles() As String
Dim aRecips() As String

were

Dim aFiles() As Variant
Dim aRecips() As Variant

I changed them in keeping with Split.

When we use SplitB we need to change the declarations back

(to

Dim aFiles() As Variant
Dim aRecips() As Variant

)

Regardless I still recommend that you use CDO. If you don't tell it your
server or any of that info, and, if your default e-mail is Outlook
Express it just uses the Outlook Express values anyway.

I changed the code on the ffdba site a little but I can't guarantee it's
any better. I'm a tad harried right now.
Nov 21 '06 #11

P: n/a
G'day

MLH wrote:
Not available in A97.
Ouch ... sorry for missing that quantifier.
The OP is not about
parsing a string. The title is "Return value of FN procedure dim'd as
Variant cannot be assigned an Array value"

Does anyone know a working method of assigning SplitB = aSplit?

This is aSplit ==Dim aSplit() As Variant ==an array of variants
I don't know why ==the following works...
>
Private Sub Command0_Click()
Dim A As Variant
Dim B(2) As String
B(0) = "qwer"
B(1) = "asdf"
B(2) = "zxcv"
A = B
Debug.Print A(0), A(1), A(2)

End Sub

but his code does not. I wonder why not? Seems like it should.
The main difference between the above code and the following is the
base array is dimensioned as a string instead of a variant.

Public Function SplitB(ByVal SplitString As String, ByVal Delimiter As
String) As Variant
Dim Position As Long
' Dim aSplit() As Variant ' try
DIM aSplit() as String
Dim Dimension As Long
Position = InStr(SplitString, Delimiter)
Do While Position <0
ReDim Preserve aSplit(Dimension)
aSplit(UBound(aSplit)) = Trim(Left(SplitString, Position - 1))
SplitString = Mid$(SplitString, Position + 1)
Dimension = Dimension + 1
Position = InStr(SplitString, Delimiter)
Loop
ReDim Preserve aSplit(Dimension)
aSplit(Dimension) = Trim(SplitString)
SplitB = aSplit
End Function

I can't test it since I do not use A97

Cheers

Keith

Nov 21 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.