failure to email workbook

[Akashwani]

Hi, I'm back, I'm trying my best to solve my problems but 2 weeks "experience" just isn't enough to understand the Excel puzzle!

This is my latest problem...

I am trying to email a workbook to numerous email addressess once the workbook has closed. This is what I have so far...

Dim bolOpening As Boolean
Dim t As Date
Private Sub Workbook_Open()
Dim s As String
If Not MsgBox(" Do you want to enter?", vbYesNo + vbQuestion, "test Database") = vbYes Then
bolOpening = True
ThisWorkbook.Close False
End If
s = InputBox("To Login In Enter your first name", "test Database")
t = Now
Const strRightAnswer As String = "Ottawa"
Dim strAnswer As String
While Not (strRightAnswer = strAnswer)
strAnswer = InputBox(" what is the capital of Canada?", "test Question")
If Not (LCase(strRightAnswer) = LCase(strAnswer)) Then MsgBox "Incorrect answer please try again."
Wend
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not bolOpening Then
MsgBox "You have been active for " & Format(Now - t, "nn") & " minutes and " & Format(Now - t, "ss") & " seconds."
Dim res
res = MsgBox(" Have you finished?", vbYesNo, "test Database")
If res <> vbYes Then Cancel = True '
End If
End Sub

Sub Mail_workbook_1()
Dim wb As Workbook
Set wb = ActiveWorkbook
If Val(Application.Version) >= 12 Then
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" & vbNewLine & _
"be no VBA code in the file you send. Save the" & vbNewLine & _
"file first as xlsm and then try the macro again.", vbInformation
Exit Sub
End If
End If
On Error Resume Next
wb.SendMail Array ("testing@email.email","testing2@email.email) _
"Testing 1 2"
On Error GoTo 0
End Sub

Obviously the email addressess are not real!

I get the following Microsoft Visual Basic message

Compile error:
Expected: list seperator or)

Any solutions?

Ak

 

[NateO]

With the following:

wb.SendMail Array ("testing@email.email","testing2@email.email) _
"Testing 1 2"

Shouldn't there be a comma following the array? Just guessing... Your compiler would see the trailing String as random text...

Edit:

Yep, you need a comma:

http://www.rondebruin.nl/mail/tips1.htm

 

[Akashwani]

Hi NateO,

I have tried the comma but get the message..

Compile error:

Expected

I am stuck, I have tried numerous options, commas ( ) whatever I can think of, all to no avail.

Ak

 

[NateO]

Post your changed code. Does it debug at a certain line of code?

Edit:

There was a missing quote in there, too! Did you try this?

wb.SendMail Array ("testing@email.email","testing2@email.email"), _
	"Testing 1 2"

*untested*

That looks like your ticket.

 

[Akashwani]

No Debug!
I have tried a few things in the offending lines
and I now get the message..

Expected: end of statement

Sub Mail_workbook_1()
Dim wb As Workbook
Set wb = ActiveWorkbook
If Val(Application.Version) >= 12 Then
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" & vbNewLine & _
"be no VBA code in the file you send. Save the" & vbNewLine & _
"file first as xlsm and then try the macro again.", vbInformation
Exit Sub
End If
End If
On Error Resume Next
wb.SendMail Array,("testing@email.email","testing2@email.email) _
"Testing 1 2"
On Error GoTo 0
End Sub

 

[NateO]

Hello, that's not where you want the comma. Make sure you see my edit in my last post, there are two offending issues - my posted code looks functional (not tested).

 

[Akashwani]

Thanks NateO,

My proof reading is obvioulsy not up to scratch, however, it has still failed to send out any emails?

I am using Excel2007 and Outlook2007

Any ideas on this?

Ak