failure to email workbook

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
With the following:

Code:
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
 
Upvote 0

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
 
Upvote 0

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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?

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

That looks like your ticket.
 
Last edited:
Upvote 0

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
 
Upvote 0

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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).
 
Upvote 0

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
 
Upvote 0

Forum statistics

Threads
1,190,825
Messages
5,983,106
Members
439,824
Latest member
nellyc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top