VBA SendObject fails after the 1 VAR instance


W

Wayne-I-M

Hi all

I have a public function that is called from a small (very simple) module.

The code fails after the 1st var instance on the send object. That is it
will send the first report then copy the second but then will not send it if
the (2nd) report is formated RTF.

I get an error message
2046 the Send Object method is not available now.

This is not an Outlook problem as the send object works fine if I change the
format to SNP – at the moment it is RTF. So the problem must be in the
access vba.

I have spent 2 days on this (mad 2 lines of code) trying everything I can
think of to find out why this would be – the keys on my keyboard that spell
google are now worn out :)

Has anyone an idea why you can send multiple instances of an SNP file but it
fails when sending an RTF

Many thanks for any help or hints you may have. I can work out most thing
so if you just have any pointers (going in the right direction) that would
help also


________________________________________

This is the basic code

Public Sub SendEmailAll_Click()

Dim varItem As Variant

For Each varItem In Me.CmbClientList.ItemsSelected

Me.txtContact1stName = CmbClientList.Column(6, varItem)
Me.txtClientID = CmbClientList.Column(0, varItem)

'THIS WORKS FINE'
DoCmd.CopyObject , ETC, ETC.

' THIS WORKS FINE '
Me.txtClientID = Me.CmbClientList.Column(0, varItem)

' THIS WORKS FINE '
DoCmd.OpenReport ETC ETC

'THIS DOES NOT WORK AFTER THE 1ST RECORD'
DoCmd.SendObject acReport, "Booking Details" & " " & Format(Date, "short
date") & " " & CmbClientList.Column(1, varItem), "RichTextFormat(*.rtf)", "E
MAIL ADDRESS DELETED FOR THIS POST ", , "", "Bookings Summery", "MESSAGE TEXT
HERE", False, ""

' THIS WORKS FINE '
DoCmd.Close acReport ETC ETC

'THIS WORKS FINE '
DoCmd.DeleteObject acReport, ETC ETC

' THIS WORKS FINE '
Next varItem

' THIS WORKS FINE '
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")

' THIS WORKS FINE '
DoCmd.Quit

End Sub


________________________________________

This is the full code - you may spot an error that I've missed ??


Public Sub SendEmailAll_Click()

Dim varItem As Variant

For Each varItem In Me.CmbClientList.ItemsSelected

Me.txtContact1stName = CmbClientList.Column(6, varItem)
Me.txtClientID = CmbClientList.Column(0, varItem)

'Copy charity and rename it'
DoCmd.CopyObject , "Booking Details" & " " & Format(Date, "short date") & "
" & CmbClientList.Column(1, varItem), acReport, "repCharityReport"

'set value to filter the report'
Me.txtClientID = Me.CmbClientList.Column(0, varItem)

'open the new report'
DoCmd.OpenReport "Booking Details" & " " & Format(Date, "short date") & " "
& CmbClientList.Column(1, varItem), acViewPreview, "",
"[DCCharityID]=[Forms]![frmReportSelector]![text34]", acHidden

'send the new report to the client'
DoCmd.SendObject acReport, "Booking Details" & " " & Format(Date, "short
date") & " " & CmbClientList.Column(1, varItem), "RichTextFormat(*.rtf)", "E
MAIL ADDRESS DELETED FOR THIS POST", , "", "Bookings Summery", "New format
automated report" & Chr(13) + Chr(10) & String(70, "_") _
& Chr(13) + Chr(10) & Chr(13) + Chr(10) & "Hi " & [txtContact1stName] &
Chr(13) + Chr(10) & Chr(13) + Chr(10) & "I have attached a report showing
details of your current bookings." & Chr(13) + Chr(10) & Chr(13) + Chr(10) &
"The report gives details up to " & Format(Now, "dd mmmm yyyy hh:nn AM/PM") &
"." & Chr(13) + Chr(10) & Chr(13) + Chr(10) & "Please note this report is
fully automated; please ensure your IT dept. adds our IP address ( IP ADDRESS
REMOVED FOR THIS POST ) and E Mail address to your white list." & Chr(13) +
Chr(10) & Chr(13) + Chr(10) & "If there are any problems with this report
please contact me." & Chr(13) + Chr(10) & Chr(13) + Chr(10) & "SOME NAME
HERE" & Chr(13) + Chr(10) & "TELEPHONE NUMBER REMOVED FOR THIS POST", False,
""

'close the new report'
DoCmd.Close acReport, "Booking Details" & " " & Format(Date, "short date") &
" " & CmbClientList.Column(1, varItem)

'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "Booking Details" & " " & Format(Date, "short
date") & " " & CmbClientList.Column(1, varItem)

'go to next item on the list and do it again'
Next varItem

'Open Outlook so the mails will send - outlook has a close action after the
send'
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")

'close access'
DoCmd.Quit

End Sub
 
Ad

Advertisements

W

Wayne-I-M

Its OK - I worked it out


--
Wayne
Manchester, England.



Wayne-I-M said:
Hi all

I have a public function that is called from a small (very simple) module.

The code fails after the 1st var instance on the send object. That is it
will send the first report then copy the second but then will not send it if
the (2nd) report is formated RTF.

I get an error message
2046 the Send Object method is not available now.

This is not an Outlook problem as the send object works fine if I change the
format to SNP – at the moment it is RTF. So the problem must be in the
access vba.

I have spent 2 days on this (mad 2 lines of code) trying everything I can
think of to find out why this would be – the keys on my keyboard that spell
google are now worn out :)

Has anyone an idea why you can send multiple instances of an SNP file but it
fails when sending an RTF

Many thanks for any help or hints you may have. I can work out most thing
so if you just have any pointers (going in the right direction) that would
help also


________________________________________

This is the basic code

Public Sub SendEmailAll_Click()

Dim varItem As Variant

For Each varItem In Me.CmbClientList.ItemsSelected

Me.txtContact1stName = CmbClientList.Column(6, varItem)
Me.txtClientID = CmbClientList.Column(0, varItem)

'THIS WORKS FINE'
DoCmd.CopyObject , ETC, ETC.

' THIS WORKS FINE '
Me.txtClientID = Me.CmbClientList.Column(0, varItem)

' THIS WORKS FINE '
DoCmd.OpenReport ETC ETC

'THIS DOES NOT WORK AFTER THE 1ST RECORD'
DoCmd.SendObject acReport, "Booking Details" & " " & Format(Date, "short
date") & " " & CmbClientList.Column(1, varItem), "RichTextFormat(*.rtf)", "E
MAIL ADDRESS DELETED FOR THIS POST ", , "", "Bookings Summery", "MESSAGE TEXT
HERE", False, ""

' THIS WORKS FINE '
DoCmd.Close acReport ETC ETC

'THIS WORKS FINE '
DoCmd.DeleteObject acReport, ETC ETC

' THIS WORKS FINE '
Next varItem

' THIS WORKS FINE '
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")

' THIS WORKS FINE '
DoCmd.Quit

End Sub


________________________________________

This is the full code - you may spot an error that I've missed ??


Public Sub SendEmailAll_Click()

Dim varItem As Variant

For Each varItem In Me.CmbClientList.ItemsSelected

Me.txtContact1stName = CmbClientList.Column(6, varItem)
Me.txtClientID = CmbClientList.Column(0, varItem)

'Copy charity and rename it'
DoCmd.CopyObject , "Booking Details" & " " & Format(Date, "short date") & "
" & CmbClientList.Column(1, varItem), acReport, "repCharityReport"

'set value to filter the report'
Me.txtClientID = Me.CmbClientList.Column(0, varItem)

'open the new report'
DoCmd.OpenReport "Booking Details" & " " & Format(Date, "short date") & " "
& CmbClientList.Column(1, varItem), acViewPreview, "",
"[DCCharityID]=[Forms]![frmReportSelector]![text34]", acHidden

'send the new report to the client'
DoCmd.SendObject acReport, "Booking Details" & " " & Format(Date, "short
date") & " " & CmbClientList.Column(1, varItem), "RichTextFormat(*.rtf)", "E
MAIL ADDRESS DELETED FOR THIS POST", , "", "Bookings Summery", "New format
automated report" & Chr(13) + Chr(10) & String(70, "_") _
& Chr(13) + Chr(10) & Chr(13) + Chr(10) & "Hi " & [txtContact1stName] &
Chr(13) + Chr(10) & Chr(13) + Chr(10) & "I have attached a report showing
details of your current bookings." & Chr(13) + Chr(10) & Chr(13) + Chr(10) &
"The report gives details up to " & Format(Now, "dd mmmm yyyy hh:nn AM/PM") &
"." & Chr(13) + Chr(10) & Chr(13) + Chr(10) & "Please note this report is
fully automated; please ensure your IT dept. adds our IP address ( IP ADDRESS
REMOVED FOR THIS POST ) and E Mail address to your white list." & Chr(13) +
Chr(10) & Chr(13) + Chr(10) & "If there are any problems with this report
please contact me." & Chr(13) + Chr(10) & Chr(13) + Chr(10) & "SOME NAME
HERE" & Chr(13) + Chr(10) & "TELEPHONE NUMBER REMOVED FOR THIS POST", False,
""

'close the new report'
DoCmd.Close acReport, "Booking Details" & " " & Format(Date, "short date") &
" " & CmbClientList.Column(1, varItem)

'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "Booking Details" & " " & Format(Date, "short
date") & " " & CmbClientList.Column(1, varItem)

'go to next item on the list and do it again'
Next varItem

'Open Outlook so the mails will send - outlook has a close action after the
send'
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")

'close access'
DoCmd.Quit

End Sub
 
B

bcap

SendObject is a law unto itself and deeply unreliable. I'd find another way
of sending your emails if I were you. Personally I like CDOSYS (lots of
examples if you Google it).

Are you happy with the formatting of your Access reports converted to RTF?
Personally I've always found this to be very unsatisfactory. PDF is much
better. You can do this natively in Access 2007, or in earlier versions you
can use Stephen Leban's free code (Google him) or a shareware product such
as Win2PDF.

Wayne-I-M said:
Hi all

I have a public function that is called from a small (very simple) module.

The code fails after the 1st var instance on the send object. That is it
will send the first report then copy the second but then will not send it
if
the (2nd) report is formated RTF.

I get an error message
2046 the Send Object method is not available now.

This is not an Outlook problem as the send object works fine if I change
the
format to SNP - at the moment it is RTF. So the problem must be in the
access vba.

I have spent 2 days on this (mad 2 lines of code) trying everything I can
think of to find out why this would be - the keys on my keyboard that
spell
google are now worn out :)

Has anyone an idea why you can send multiple instances of an SNP file but
it
fails when sending an RTF

Many thanks for any help or hints you may have. I can work out most thing
so if you just have any pointers (going in the right direction) that would
help also


________________________________________

This is the basic code

Public Sub SendEmailAll_Click()

Dim varItem As Variant

For Each varItem In Me.CmbClientList.ItemsSelected

Me.txtContact1stName = CmbClientList.Column(6, varItem)
Me.txtClientID = CmbClientList.Column(0, varItem)

'THIS WORKS FINE'
DoCmd.CopyObject , ETC, ETC.

' THIS WORKS FINE '
Me.txtClientID = Me.CmbClientList.Column(0, varItem)

' THIS WORKS FINE '
DoCmd.OpenReport ETC ETC

'THIS DOES NOT WORK AFTER THE 1ST RECORD'
DoCmd.SendObject acReport, "Booking Details" & " " & Format(Date, "short
date") & " " & CmbClientList.Column(1, varItem), "RichTextFormat(*.rtf)",
"E
MAIL ADDRESS DELETED FOR THIS POST ", , "", "Bookings Summery", "MESSAGE
TEXT
HERE", False, ""

' THIS WORKS FINE '
DoCmd.Close acReport ETC ETC

'THIS WORKS FINE '
DoCmd.DeleteObject acReport, ETC ETC

' THIS WORKS FINE '
Next varItem

' THIS WORKS FINE '
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")

' THIS WORKS FINE '
DoCmd.Quit

End Sub


________________________________________

This is the full code - you may spot an error that I've missed ??


Public Sub SendEmailAll_Click()

Dim varItem As Variant

For Each varItem In Me.CmbClientList.ItemsSelected

Me.txtContact1stName = CmbClientList.Column(6, varItem)
Me.txtClientID = CmbClientList.Column(0, varItem)

'Copy charity and rename it'
DoCmd.CopyObject , "Booking Details" & " " & Format(Date, "short date") &
"
" & CmbClientList.Column(1, varItem), acReport, "repCharityReport"

'set value to filter the report'
Me.txtClientID = Me.CmbClientList.Column(0, varItem)

'open the new report'
DoCmd.OpenReport "Booking Details" & " " & Format(Date, "short date") & "
"
& CmbClientList.Column(1, varItem), acViewPreview, "",
"[DCCharityID]=[Forms]![frmReportSelector]![text34]", acHidden

'send the new report to the client'
DoCmd.SendObject acReport, "Booking Details" & " " & Format(Date, "short
date") & " " & CmbClientList.Column(1, varItem), "RichTextFormat(*.rtf)",
"E
MAIL ADDRESS DELETED FOR THIS POST", , "", "Bookings Summery", "New format
automated report" & Chr(13) + Chr(10) & String(70, "_") _
& Chr(13) + Chr(10) & Chr(13) + Chr(10) & "Hi " & [txtContact1stName] &
Chr(13) + Chr(10) & Chr(13) + Chr(10) & "I have attached a report showing
details of your current bookings." & Chr(13) + Chr(10) & Chr(13) + Chr(10)
&
"The report gives details up to " & Format(Now, "dd mmmm yyyy hh:nn
AM/PM") &
"." & Chr(13) + Chr(10) & Chr(13) + Chr(10) & "Please note this report is
fully automated; please ensure your IT dept. adds our IP address ( IP
ADDRESS
REMOVED FOR THIS POST ) and E Mail address to your white list." & Chr(13)
+
Chr(10) & Chr(13) + Chr(10) & "If there are any problems with this report
please contact me." & Chr(13) + Chr(10) & Chr(13) + Chr(10) & "SOME NAME
HERE" & Chr(13) + Chr(10) & "TELEPHONE NUMBER REMOVED FOR THIS POST",
False,
""

'close the new report'
DoCmd.Close acReport, "Booking Details" & " " & Format(Date, "short date")
&
" " & CmbClientList.Column(1, varItem)

'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "Booking Details" & " " & Format(Date, "short
date") & " " & CmbClientList.Column(1, varItem)

'go to next item on the list and do it again'
Next varItem

'Open Outlook so the mails will send - outlook has a close action after
the
send'
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")

'close access'
DoCmd.Quit

End Sub
 
W

Wayne-I-M

Thanks for the feeback

I don't want to send out SNP files. I was just trying work out why SNP
format works fine and RTF does not.

Probelm solved - I gave up - am sending out excel files now.

I am (slowly) creating a completly new DB in 2007 vista. The sendobject is
not the best but it's simple to use and (once you get it right ???) tends not
to go wrong and I have one frontend with the clickyes so this works fine

The problem I have, is the same as most people on this forum is that every
department wants thier own "stuff" and trying to keep all depts is, to say
the least, difficult.

The new DB will send as PDF format - but trying to keep clients happy is
what we do

: - )



--
Wayne
Manchester, England.



bcap said:
SendObject is a law unto itself and deeply unreliable. I'd find another way
of sending your emails if I were you. Personally I like CDOSYS (lots of
examples if you Google it).

Are you happy with the formatting of your Access reports converted to RTF?
Personally I've always found this to be very unsatisfactory. PDF is much
better. You can do this natively in Access 2007, or in earlier versions you
can use Stephen Leban's free code (Google him) or a shareware product such
as Win2PDF.

Wayne-I-M said:
Hi all

I have a public function that is called from a small (very simple) module.

The code fails after the 1st var instance on the send object. That is it
will send the first report then copy the second but then will not send it
if
the (2nd) report is formated RTF.

I get an error message
2046 the Send Object method is not available now.

This is not an Outlook problem as the send object works fine if I change
the
format to SNP - at the moment it is RTF. So the problem must be in the
access vba.

I have spent 2 days on this (mad 2 lines of code) trying everything I can
think of to find out why this would be - the keys on my keyboard that
spell
google are now worn out :)

Has anyone an idea why you can send multiple instances of an SNP file but
it
fails when sending an RTF

Many thanks for any help or hints you may have. I can work out most thing
so if you just have any pointers (going in the right direction) that would
help also


________________________________________

This is the basic code

Public Sub SendEmailAll_Click()

Dim varItem As Variant

For Each varItem In Me.CmbClientList.ItemsSelected

Me.txtContact1stName = CmbClientList.Column(6, varItem)
Me.txtClientID = CmbClientList.Column(0, varItem)

'THIS WORKS FINE'
DoCmd.CopyObject , ETC, ETC.

' THIS WORKS FINE '
Me.txtClientID = Me.CmbClientList.Column(0, varItem)

' THIS WORKS FINE '
DoCmd.OpenReport ETC ETC

'THIS DOES NOT WORK AFTER THE 1ST RECORD'
DoCmd.SendObject acReport, "Booking Details" & " " & Format(Date, "short
date") & " " & CmbClientList.Column(1, varItem), "RichTextFormat(*.rtf)",
"E
MAIL ADDRESS DELETED FOR THIS POST ", , "", "Bookings Summery", "MESSAGE
TEXT
HERE", False, ""

' THIS WORKS FINE '
DoCmd.Close acReport ETC ETC

'THIS WORKS FINE '
DoCmd.DeleteObject acReport, ETC ETC

' THIS WORKS FINE '
Next varItem

' THIS WORKS FINE '
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")

' THIS WORKS FINE '
DoCmd.Quit

End Sub


________________________________________

This is the full code - you may spot an error that I've missed ??


Public Sub SendEmailAll_Click()

Dim varItem As Variant

For Each varItem In Me.CmbClientList.ItemsSelected

Me.txtContact1stName = CmbClientList.Column(6, varItem)
Me.txtClientID = CmbClientList.Column(0, varItem)

'Copy charity and rename it'
DoCmd.CopyObject , "Booking Details" & " " & Format(Date, "short date") &
"
" & CmbClientList.Column(1, varItem), acReport, "repCharityReport"

'set value to filter the report'
Me.txtClientID = Me.CmbClientList.Column(0, varItem)

'open the new report'
DoCmd.OpenReport "Booking Details" & " " & Format(Date, "short date") & "
"
& CmbClientList.Column(1, varItem), acViewPreview, "",
"[DCCharityID]=[Forms]![frmReportSelector]![text34]", acHidden

'send the new report to the client'
DoCmd.SendObject acReport, "Booking Details" & " " & Format(Date, "short
date") & " " & CmbClientList.Column(1, varItem), "RichTextFormat(*.rtf)",
"E
MAIL ADDRESS DELETED FOR THIS POST", , "", "Bookings Summery", "New format
automated report" & Chr(13) + Chr(10) & String(70, "_") _
& Chr(13) + Chr(10) & Chr(13) + Chr(10) & "Hi " & [txtContact1stName] &
Chr(13) + Chr(10) & Chr(13) + Chr(10) & "I have attached a report showing
details of your current bookings." & Chr(13) + Chr(10) & Chr(13) + Chr(10)
&
"The report gives details up to " & Format(Now, "dd mmmm yyyy hh:nn
AM/PM") &
"." & Chr(13) + Chr(10) & Chr(13) + Chr(10) & "Please note this report is
fully automated; please ensure your IT dept. adds our IP address ( IP
ADDRESS
REMOVED FOR THIS POST ) and E Mail address to your white list." & Chr(13)
+
Chr(10) & Chr(13) + Chr(10) & "If there are any problems with this report
please contact me." & Chr(13) + Chr(10) & Chr(13) + Chr(10) & "SOME NAME
HERE" & Chr(13) + Chr(10) & "TELEPHONE NUMBER REMOVED FOR THIS POST",
False,
""

'close the new report'
DoCmd.Close acReport, "Booking Details" & " " & Format(Date, "short date")
&
" " & CmbClientList.Column(1, varItem)

'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "Booking Details" & " " & Format(Date, "short
date") & " " & CmbClientList.Column(1, varItem)

'go to next item on the list and do it again'
Next varItem

'Open Outlook so the mails will send - outlook has a close action after
the
send'
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")

'close access'
DoCmd.Quit

End Sub
 
B

bcap

Hi again,

I certainly didn't and wouldn't suggest SNP, it is a truly gruesome format.

If you intend to distribute your application, be prepared for SendObject to
work on some computers but (mysteriously and inexplicably) not work on
others.

Wayne-I-M said:
Thanks for the feeback

I don't want to send out SNP files. I was just trying work out why SNP
format works fine and RTF does not.

Probelm solved - I gave up - am sending out excel files now.

I am (slowly) creating a completly new DB in 2007 vista. The sendobject
is
not the best but it's simple to use and (once you get it right ???) tends
not
to go wrong and I have one frontend with the clickyes so this works fine

The problem I have, is the same as most people on this forum is that every
department wants thier own "stuff" and trying to keep all depts is, to say
the least, difficult.

The new DB will send as PDF format - but trying to keep clients happy is
what we do

: - )



--
Wayne
Manchester, England.



bcap said:
SendObject is a law unto itself and deeply unreliable. I'd find another
way
of sending your emails if I were you. Personally I like CDOSYS (lots of
examples if you Google it).

Are you happy with the formatting of your Access reports converted to
RTF?
Personally I've always found this to be very unsatisfactory. PDF is much
better. You can do this natively in Access 2007, or in earlier versions
you
can use Stephen Leban's free code (Google him) or a shareware product
such
as Win2PDF.

Wayne-I-M said:
Hi all

I have a public function that is called from a small (very simple)
module.

The code fails after the 1st var instance on the send object. That is
it
will send the first report then copy the second but then will not send
it
if
the (2nd) report is formated RTF.

I get an error message
2046 the Send Object method is not available now.

This is not an Outlook problem as the send object works fine if I
change
the
format to SNP - at the moment it is RTF. So the problem must be in the
access vba.

I have spent 2 days on this (mad 2 lines of code) trying everything I
can
think of to find out why this would be - the keys on my keyboard that
spell
google are now worn out :)

Has anyone an idea why you can send multiple instances of an SNP file
but
it
fails when sending an RTF

Many thanks for any help or hints you may have. I can work out most
thing
so if you just have any pointers (going in the right direction) that
would
help also


________________________________________

This is the basic code

Public Sub SendEmailAll_Click()

Dim varItem As Variant

For Each varItem In Me.CmbClientList.ItemsSelected

Me.txtContact1stName = CmbClientList.Column(6, varItem)
Me.txtClientID = CmbClientList.Column(0, varItem)

'THIS WORKS FINE'
DoCmd.CopyObject , ETC, ETC.

' THIS WORKS FINE '
Me.txtClientID = Me.CmbClientList.Column(0, varItem)

' THIS WORKS FINE '
DoCmd.OpenReport ETC ETC

'THIS DOES NOT WORK AFTER THE 1ST RECORD'
DoCmd.SendObject acReport, "Booking Details" & " " & Format(Date,
"short
date") & " " & CmbClientList.Column(1, varItem),
"RichTextFormat(*.rtf)",
"E
MAIL ADDRESS DELETED FOR THIS POST ", , "", "Bookings Summery",
"MESSAGE
TEXT
HERE", False, ""

' THIS WORKS FINE '
DoCmd.Close acReport ETC ETC

'THIS WORKS FINE '
DoCmd.DeleteObject acReport, ETC ETC

' THIS WORKS FINE '
Next varItem

' THIS WORKS FINE '
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")

' THIS WORKS FINE '
DoCmd.Quit

End Sub


________________________________________

This is the full code - you may spot an error that I've missed ??


Public Sub SendEmailAll_Click()

Dim varItem As Variant

For Each varItem In Me.CmbClientList.ItemsSelected

Me.txtContact1stName = CmbClientList.Column(6, varItem)
Me.txtClientID = CmbClientList.Column(0, varItem)

'Copy charity and rename it'
DoCmd.CopyObject , "Booking Details" & " " & Format(Date, "short date")
&
"
" & CmbClientList.Column(1, varItem), acReport, "repCharityReport"

'set value to filter the report'
Me.txtClientID = Me.CmbClientList.Column(0, varItem)

'open the new report'
DoCmd.OpenReport "Booking Details" & " " & Format(Date, "short date") &
"
"
& CmbClientList.Column(1, varItem), acViewPreview, "",
"[DCCharityID]=[Forms]![frmReportSelector]![text34]", acHidden

'send the new report to the client'
DoCmd.SendObject acReport, "Booking Details" & " " & Format(Date,
"short
date") & " " & CmbClientList.Column(1, varItem),
"RichTextFormat(*.rtf)",
"E
MAIL ADDRESS DELETED FOR THIS POST", , "", "Bookings Summery", "New
format
automated report" & Chr(13) + Chr(10) & String(70, "_") _
& Chr(13) + Chr(10) & Chr(13) + Chr(10) & "Hi " & [txtContact1stName] &
Chr(13) + Chr(10) & Chr(13) + Chr(10) & "I have attached a report
showing
details of your current bookings." & Chr(13) + Chr(10) & Chr(13) +
Chr(10)
&
"The report gives details up to " & Format(Now, "dd mmmm yyyy hh:nn
AM/PM") &
"." & Chr(13) + Chr(10) & Chr(13) + Chr(10) & "Please note this report
is
fully automated; please ensure your IT dept. adds our IP address ( IP
ADDRESS
REMOVED FOR THIS POST ) and E Mail address to your white list." &
Chr(13)
+
Chr(10) & Chr(13) + Chr(10) & "If there are any problems with this
report
please contact me." & Chr(13) + Chr(10) & Chr(13) + Chr(10) & "SOME
NAME
HERE" & Chr(13) + Chr(10) & "TELEPHONE NUMBER REMOVED FOR THIS POST",
False,
""

'close the new report'
DoCmd.Close acReport, "Booking Details" & " " & Format(Date, "short
date")
&
" " & CmbClientList.Column(1, varItem)

'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "Booking Details" & " " & Format(Date,
"short
date") & " " & CmbClientList.Column(1, varItem)

'go to next item on the list and do it again'
Next varItem

'Open Outlook so the mails will send - outlook has a close action after
the
send'
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")

'close access'
DoCmd.Quit

End Sub
 
W

Wayne-I-M

NO its not for distrubution. In fact this is proberbly the last major
upgrade to this DB. The new (2k7) should be ready for testing in a month or
so and be up and running by the autum

This problem will be here with the new DB and I have loaded an auto-upload
to an asp subsite so clients will be able to login to thier account to get an
up to date report. I am trying to get rid completly of the mailed reports

Thanks again
--
Wayne
Manchester, England.



bcap said:
Hi again,

I certainly didn't and wouldn't suggest SNP, it is a truly gruesome format.

If you intend to distribute your application, be prepared for SendObject to
work on some computers but (mysteriously and inexplicably) not work on
others.

Wayne-I-M said:
Thanks for the feeback

I don't want to send out SNP files. I was just trying work out why SNP
format works fine and RTF does not.

Probelm solved - I gave up - am sending out excel files now.

I am (slowly) creating a completly new DB in 2007 vista. The sendobject
is
not the best but it's simple to use and (once you get it right ???) tends
not
to go wrong and I have one frontend with the clickyes so this works fine

The problem I have, is the same as most people on this forum is that every
department wants thier own "stuff" and trying to keep all depts is, to say
the least, difficult.

The new DB will send as PDF format - but trying to keep clients happy is
what we do

: - )



--
Wayne
Manchester, England.



bcap said:
SendObject is a law unto itself and deeply unreliable. I'd find another
way
of sending your emails if I were you. Personally I like CDOSYS (lots of
examples if you Google it).

Are you happy with the formatting of your Access reports converted to
RTF?
Personally I've always found this to be very unsatisfactory. PDF is much
better. You can do this natively in Access 2007, or in earlier versions
you
can use Stephen Leban's free code (Google him) or a shareware product
such
as Win2PDF.

Hi all

I have a public function that is called from a small (very simple)
module.

The code fails after the 1st var instance on the send object. That is
it
will send the first report then copy the second but then will not send
it
if
the (2nd) report is formated RTF.

I get an error message
2046 the Send Object method is not available now.

This is not an Outlook problem as the send object works fine if I
change
the
format to SNP - at the moment it is RTF. So the problem must be in the
access vba.

I have spent 2 days on this (mad 2 lines of code) trying everything I
can
think of to find out why this would be - the keys on my keyboard that
spell
google are now worn out :)

Has anyone an idea why you can send multiple instances of an SNP file
but
it
fails when sending an RTF

Many thanks for any help or hints you may have. I can work out most
thing
so if you just have any pointers (going in the right direction) that
would
help also


________________________________________

This is the basic code

Public Sub SendEmailAll_Click()

Dim varItem As Variant

For Each varItem In Me.CmbClientList.ItemsSelected

Me.txtContact1stName = CmbClientList.Column(6, varItem)
Me.txtClientID = CmbClientList.Column(0, varItem)

'THIS WORKS FINE'
DoCmd.CopyObject , ETC, ETC.

' THIS WORKS FINE '
Me.txtClientID = Me.CmbClientList.Column(0, varItem)

' THIS WORKS FINE '
DoCmd.OpenReport ETC ETC

'THIS DOES NOT WORK AFTER THE 1ST RECORD'
DoCmd.SendObject acReport, "Booking Details" & " " & Format(Date,
"short
date") & " " & CmbClientList.Column(1, varItem),
"RichTextFormat(*.rtf)",
"E
MAIL ADDRESS DELETED FOR THIS POST ", , "", "Bookings Summery",
"MESSAGE
TEXT
HERE", False, ""

' THIS WORKS FINE '
DoCmd.Close acReport ETC ETC

'THIS WORKS FINE '
DoCmd.DeleteObject acReport, ETC ETC

' THIS WORKS FINE '
Next varItem

' THIS WORKS FINE '
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")

' THIS WORKS FINE '
DoCmd.Quit

End Sub


________________________________________

This is the full code - you may spot an error that I've missed ??


Public Sub SendEmailAll_Click()

Dim varItem As Variant

For Each varItem In Me.CmbClientList.ItemsSelected

Me.txtContact1stName = CmbClientList.Column(6, varItem)
Me.txtClientID = CmbClientList.Column(0, varItem)

'Copy charity and rename it'
DoCmd.CopyObject , "Booking Details" & " " & Format(Date, "short date")
&
"
" & CmbClientList.Column(1, varItem), acReport, "repCharityReport"

'set value to filter the report'
Me.txtClientID = Me.CmbClientList.Column(0, varItem)

'open the new report'
DoCmd.OpenReport "Booking Details" & " " & Format(Date, "short date") &
"
"
& CmbClientList.Column(1, varItem), acViewPreview, "",
"[DCCharityID]=[Forms]![frmReportSelector]![text34]", acHidden

'send the new report to the client'
DoCmd.SendObject acReport, "Booking Details" & " " & Format(Date,
"short
date") & " " & CmbClientList.Column(1, varItem),
"RichTextFormat(*.rtf)",
"E
MAIL ADDRESS DELETED FOR THIS POST", , "", "Bookings Summery", "New
format
automated report" & Chr(13) + Chr(10) & String(70, "_") _
& Chr(13) + Chr(10) & Chr(13) + Chr(10) & "Hi " & [txtContact1stName] &
Chr(13) + Chr(10) & Chr(13) + Chr(10) & "I have attached a report
showing
details of your current bookings." & Chr(13) + Chr(10) & Chr(13) +
Chr(10)
&
"The report gives details up to " & Format(Now, "dd mmmm yyyy hh:nn
AM/PM") &
"." & Chr(13) + Chr(10) & Chr(13) + Chr(10) & "Please note this report
is
fully automated; please ensure your IT dept. adds our IP address ( IP
ADDRESS
REMOVED FOR THIS POST ) and E Mail address to your white list." &
Chr(13)
+
Chr(10) & Chr(13) + Chr(10) & "If there are any problems with this
report
please contact me." & Chr(13) + Chr(10) & Chr(13) + Chr(10) & "SOME
NAME
HERE" & Chr(13) + Chr(10) & "TELEPHONE NUMBER REMOVED FOR THIS POST",
False,
""

'close the new report'
DoCmd.Close acReport, "Booking Details" & " " & Format(Date, "short
date")
&
" " & CmbClientList.Column(1, varItem)

'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "Booking Details" & " " & Format(Date,
"short
date") & " " & CmbClientList.Column(1, varItem)

'go to next item on the list and do it again'
Next varItem

'Open Outlook so the mails will send - outlook has a close action after
the
send'
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")

'close access'
DoCmd.Quit

End Sub
 
Ad

Advertisements

A

a a r o n . k e m p f

nothing wrong with SNP.. except for the fact that MS didn't apply the
same concept to Excel

Basically-- we've had FOUR different 'portable document formats'
jammed down our throat in the past decade

SNP -> MDI -> XPS -> PDF

WTF?

I think that SNP rocks. Excel didn't have a portable document format
for FIVE YEARS after Access did.
And sending spreadsheets? There are MAJOR security problems with that
as well





Hi again,

I certainly didn't and wouldn't suggest SNP, it is a truly gruesome format.

If you intend to distribute your application, be prepared for SendObject to
work on some computers but (mysteriously and inexplicably) not work on
others.


Thanks for the feeback
I don't want to send out SNP files.  I was just trying work out why SNP
format works fine and RTF does not.
Probelm solved - I gave up - am sending out excel files now.
I am (slowly) creating a completly new DB in 2007 vista.  The sendobject
is
not the best but it's simple to use and (once you get it right ???) tends
not
to go wrong and I have one frontend with the clickyes so this works fine
The problem I have, is the same as most people on this forum is that every
department wants thier own "stuff" and trying to keep all depts is, to say
the least, difficult.
The new DB will send as PDF format - but trying to keep clients happy is
what we do
SendObject is a law unto itself and deeply unreliable.  I'd find another
way
of sending your emails if I were you.  Personally I like CDOSYS (lots of
examples if you Google it).
Are you happy with the formatting of your Access reports converted to
RTF?
Personally I've always found this to be very unsatisfactory.  PDF ismuch
better.  You can do this natively in Access 2007, or in earlier versions
you
can use Stephen Leban's free code (Google him) or a shareware product
such
as Win2PDF.
Hi all
I have a public function that is called from a small (very simple)
module.
The code fails after the 1st var instance on the send object. That is
it
will send the first report then copy the second but then will not send
it
if
the (2nd) report is formated RTF.
I get an error message
2046 the Send Object method is not available now.
This is not an Outlook problem as the send object works fine if I
change
the
format to SNP - at the moment it is RTF.  So the problem must be in the
access vba.
I have spent 2 days on this (mad 2 lines of code) trying everything I
can
think of to find out why this would be - the keys on my keyboard that
spell
google are now worn out :)
Has anyone an idea why you can send multiple instances of an SNP file
but
it
fails when sending an RTF
Many thanks for any help or hints you may have.  I can work out most
thing
so if you just have any pointers (going in the right direction) that
would
help also
________________________________________
This is the basic code
Public Sub SendEmailAll_Click()
Dim varItem As Variant
For Each varItem In Me.CmbClientList.ItemsSelected
Me.txtContact1stName = CmbClientList.Column(6, varItem)
Me.txtClientID = CmbClientList.Column(0, varItem)
'THIS WORKS FINE'
DoCmd.CopyObject , ETC, ETC.
' THIS WORKS FINE '
Me.txtClientID = Me.CmbClientList.Column(0, varItem)
' THIS WORKS FINE '
DoCmd.OpenReport ETC ETC
'THIS DOES NOT WORK AFTER THE 1ST RECORD'
DoCmd.SendObject acReport, "Booking Details" & " " & Format(Date,
"short
date") & " " & CmbClientList.Column(1, varItem),
"RichTextFormat(*.rtf)",
"E
MAIL ADDRESS DELETED FOR THIS POST ", , "", "Bookings Summery",
"MESSAGE
TEXT
HERE", False, ""
' THIS WORKS FINE '
DoCmd.Close acReport ETC ETC
'THIS WORKS FINE '
DoCmd.DeleteObject acReport, ETC ETC
' THIS WORKS FINE '
Next varItem
' THIS WORKS FINE '
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")
' THIS WORKS FINE '
DoCmd.Quit
End Sub
________________________________________
This is the full code - you may spot an error that I've missed ??
Public Sub SendEmailAll_Click()
Dim varItem As Variant
For Each varItem In Me.CmbClientList.ItemsSelected
Me.txtContact1stName = CmbClientList.Column(6, varItem)
Me.txtClientID = CmbClientList.Column(0, varItem)
'Copy charity and rename it'
DoCmd.CopyObject , "Booking Details" & " " & Format(Date, "short date")
&
"
" & CmbClientList.Column(1, varItem), acReport, "repCharityReport"
'set value to filter the report'
Me.txtClientID = Me.CmbClientList.Column(0, varItem)
'open the new report'
DoCmd.OpenReport "Booking Details" & " " & Format(Date, "short date") &
"
"
& CmbClientList.Column(1, varItem), acViewPreview, "",
"[DCCharityID]=[Forms]![frmReportSelector]![text34]", acHidden
'send the new report to the client'
DoCmd.SendObject acReport, "Booking Details" & " " & Format(Date,
"short
date") & " " & CmbClientList.Column(1, varItem),
"RichTextFormat(*.rtf)",
"E
MAIL ADDRESS DELETED FOR THIS POST", , "", "Bookings Summery", "New
format
automated report" & Chr(13) + Chr(10) & String(70, "_") _
& Chr(13) + Chr(10) & Chr(13) + Chr(10) & "Hi " & [txtContact1stName] &
Chr(13) + Chr(10) & Chr(13) + Chr(10) & "I have attached a report
showing
details of your current bookings." & Chr(13) + Chr(10) & Chr(13) +
Chr(10)
&
"The report gives details up to " & Format(Now, "dd mmmm yyyy hh:nn
AM/PM") &
"." & Chr(13) + Chr(10) & Chr(13) + Chr(10) & "Please note this report
is
fully automated; please ensure your IT dept. adds our IP address ( IP
ADDRESS
REMOVED FOR THIS POST ) and E Mail address to your white list." &
Chr(13)
+
Chr(10) & Chr(13) + Chr(10) & "If there are any problems with this
report
please contact me." & Chr(13) + Chr(10) & Chr(13) + Chr(10) & "SOME
NAME
HERE" & Chr(13) + Chr(10) & "TELEPHONE NUMBER REMOVED FOR THIS POST",
False,
""
'close the new report'
DoCmd.Close acReport, "Booking Details" & " " & Format(Date, "short
date")
&
" " & CmbClientList.Column(1, varItem)
'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "Booking Details" & " " & Format(Date,
"short
date") & " " & CmbClientList.Column(1, varItem)
'go to next item on the list and do it again'
Next varItem
'Open Outlook so the mails will send - outlook has a close action after
the
send'
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")
'close access'
DoCmd.Quit
End Sub
 
A

a a r o n . k e m p f

so how are you getting the ACCDB format to work with ASP?

I'm dead serious





NO its not for distrubution.  In fact this is proberbly the last major
upgrade to this DB.  The new (2k7) should be ready for testing in a month or
so and be up and running by the autum

This problem will be here with the new DB and I have loaded an auto-upload
to an asp subsite so clients will be able to login to thier account to get an
up to date report.  I am trying to get rid completly of the mailed reports

Thanks again
--
Wayne
Manchester, England.

bcap said:
Hi again,
I certainly didn't and wouldn't suggest SNP, it is a truly gruesome format.
If you intend to distribute your application, be prepared for SendObject to
work on some computers but (mysteriously and inexplicably) not work on
others.
Wayne-I-M said:
Thanks for the feeback
I don't want to send out SNP files.  I was just trying work out whySNP
format works fine and RTF does not.
Probelm solved - I gave up - am sending out excel files now.
I am (slowly) creating a completly new DB in 2007 vista.  The sendobject
is
not the best but it's simple to use and (once you get it right ???) tends
not
to go wrong and I have one frontend with the clickyes so this works fine
The problem I have, is the same as most people on this forum is that every
department wants thier own "stuff" and trying to keep all depts is, to say
the least, difficult.
The new DB will send as PDF format - but trying to keep clients happyis
what we do
: - )
--
Wayne
Manchester, England.
:
SendObject is a law unto itself and deeply unreliable.  I'd find another
way
of sending your emails if I were you.  Personally I like CDOSYS (lots of
examples if you Google it).
Are you happy with the formatting of your Access reports converted to
RTF?
Personally I've always found this to be very unsatisfactory.  PDF is much
better.  You can do this natively in Access 2007, or in earlier versions
you
can use Stephen Leban's free code (Google him) or a shareware product
such
as Win2PDF.
Hi all
I have a public function that is called from a small (very simple)
module.
The code fails after the 1st var instance on the send object. Thatis
it
will send the first report then copy the second but then will not send
it
if
the (2nd) report is formated RTF.
I get an error message
2046 the Send Object method is not available now.
This is not an Outlook problem as the send object works fine if I
change
the
format to SNP - at the moment it is RTF.  So the problem must bein the
access vba.
I have spent 2 days on this (mad 2 lines of code) trying everything I
can
think of to find out why this would be - the keys on my keyboard that
spell
google are now worn out :)
Has anyone an idea why you can send multiple instances of an SNP file
but
it
fails when sending an RTF
Many thanks for any help or hints you may have.  I can work out most
thing
so if you just have any pointers (going in the right direction) that
would
help also
________________________________________
This is the basic code
Public Sub SendEmailAll_Click()
Dim varItem As Variant
For Each varItem In Me.CmbClientList.ItemsSelected
Me.txtContact1stName = CmbClientList.Column(6, varItem)
Me.txtClientID = CmbClientList.Column(0, varItem)
'THIS WORKS FINE'
DoCmd.CopyObject , ETC, ETC.
' THIS WORKS FINE '
Me.txtClientID = Me.CmbClientList.Column(0, varItem)
' THIS WORKS FINE '
DoCmd.OpenReport ETC ETC
'THIS DOES NOT WORK AFTER THE 1ST RECORD'
DoCmd.SendObject acReport, "Booking Details" & " " & Format(Date,
"short
date") & " " & CmbClientList.Column(1, varItem),
"RichTextFormat(*.rtf)",
"E
MAIL ADDRESS DELETED FOR THIS POST ", , "", "Bookings Summery",
"MESSAGE
TEXT
HERE", False, ""
' THIS WORKS FINE '
DoCmd.Close acReport ETC ETC
'THIS WORKS FINE '
DoCmd.DeleteObject acReport, ETC ETC
' THIS WORKS FINE '
Next varItem
' THIS WORKS FINE '
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")
' THIS WORKS FINE '
DoCmd.Quit
End Sub
________________________________________
This is the full code - you may spot an error that I've missed ??
Public Sub SendEmailAll_Click()
Dim varItem As Variant
For Each varItem In Me.CmbClientList.ItemsSelected
Me.txtContact1stName = CmbClientList.Column(6, varItem)
Me.txtClientID = CmbClientList.Column(0, varItem)
'Copy charity and rename it'
DoCmd.CopyObject , "Booking Details" & " " & Format(Date, "short date")
&
"
" & CmbClientList.Column(1, varItem), acReport, "repCharityReport"
'set value to filter the report'
Me.txtClientID = Me.CmbClientList.Column(0, varItem)
'open the new report'
DoCmd.OpenReport "Booking Details" & " " & Format(Date, "short date") &
"
"
& CmbClientList.Column(1, varItem), acViewPreview, "",
"[DCCharityID]=[Forms]![frmReportSelector]![text34]", acHidden
'send the new report to the client'
DoCmd.SendObject acReport, "Booking Details" & " " & Format(Date,
"short
date") & " " & CmbClientList.Column(1, varItem),
"RichTextFormat(*.rtf)",
"E
MAIL ADDRESS DELETED FOR THIS POST", , "", "Bookings Summery", "New
format
automated report" & Chr(13) + Chr(10) & String(70, "_") _
& Chr(13) + Chr(10) & Chr(13) + Chr(10) & "Hi " & [txtContact1stName] &
Chr(13) + Chr(10) & Chr(13) + Chr(10) & "I have attached a report
showing
details of your current bookings." & Chr(13) + Chr(10) & Chr(13) +
Chr(10)
&
"The report gives details up to " & Format(Now, "dd mmmm yyyy hh:nn
AM/PM") &
"." & Chr(13) + Chr(10) & Chr(13) + Chr(10) & "Please note this report
is
fully automated; please ensure your IT dept. adds our IP address (IP
ADDRESS
REMOVED FOR THIS POST ) and E Mail address to your white list." &
Chr(13)
+
Chr(10) & Chr(13) + Chr(10) & "If there are any problems with this
report
please contact me." & Chr(13) + Chr(10) & Chr(13) + Chr(10) & "SOME
NAME
HERE" & Chr(13) + Chr(10) & "TELEPHONE NUMBER REMOVED FOR THIS POST",
False,
""
'close the new report'
DoCmd.Close acReport, "Booking Details" & " " & Format(Date, "short
date")
&
" " & CmbClientList.Column(1, varItem)
'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "Booking Details" & " " & Format(Date,
"short
date") & " " & CmbClientList.Column(1, varItem)
'go to next item on the list and do it again'
Next varItem
'Open Outlook so the mails will send - outlook has a close action after
the
send'
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")
'close access'
DoCmd.Quit
End Sub
 
W

Wayne-I-M

We aren't. Just going to link the ASP page ith a sub-site and give access on
password to login. The login will will diverted to a really simple query
lead report

and I KNOW yo're not going to like this. Are you sitting down Arron

The database I have choosen to use is...... (need a drum roll here) .....
Access 2k7

LoL - did look at using sql server which is only ready hosted with our
on-line stuff but (in my opion only) access will enable us to do what we
need better. Sorry Arron - you can get up off the floor now


--
Wayne
Manchester, England.



a a r o n . k e m p f @ g m a i l . c o said:
so how are you getting the ACCDB format to work with ASP?

I'm dead serious





NO its not for distrubution. In fact this is proberbly the last major
upgrade to this DB. The new (2k7) should be ready for testing in a month or
so and be up and running by the autum

This problem will be here with the new DB and I have loaded an auto-upload
to an asp subsite so clients will be able to login to thier account to get an
up to date report. I am trying to get rid completly of the mailed reports

Thanks again
--
Wayne
Manchester, England.

bcap said:
Hi again,
I certainly didn't and wouldn't suggest SNP, it is a truly gruesome format.
If you intend to distribute your application, be prepared for SendObject to
work on some computers but (mysteriously and inexplicably) not work on
others.
Thanks for the feeback
I don't want to send out SNP files. I was just trying work out why SNP
format works fine and RTF does not.
Probelm solved - I gave up - am sending out excel files now.
I am (slowly) creating a completly new DB in 2007 vista. The sendobject
is
not the best but it's simple to use and (once you get it right ???) tends
not
to go wrong and I have one frontend with the clickyes so this works fine
The problem I have, is the same as most people on this forum is that every
department wants thier own "stuff" and trying to keep all depts is, to say
the least, difficult.
The new DB will send as PDF format - but trying to keep clients happy is
what we do
"bcap" wrote:
SendObject is a law unto itself and deeply unreliable. I'd find another
way
of sending your emails if I were you. Personally I like CDOSYS (lots of
examples if you Google it).
Are you happy with the formatting of your Access reports converted to
RTF?
Personally I've always found this to be very unsatisfactory. PDF is much
better. You can do this natively in Access 2007, or in earlier versions
you
can use Stephen Leban's free code (Google him) or a shareware product
such
as Win2PDF.
I have a public function that is called from a small (very simple)
module.
The code fails after the 1st var instance on the send object. That is
it
will send the first report then copy the second but then will not send
it
if
the (2nd) report is formated RTF.
I get an error message
2046 the Send Object method is not available now.
This is not an Outlook problem as the send object works fine if I
change
the
format to SNP - at the moment it is RTF. So the problem must be in the
access vba.
I have spent 2 days on this (mad 2 lines of code) trying everything I
can
think of to find out why this would be - the keys on my keyboard that
spell
google are now worn out :)
Has anyone an idea why you can send multiple instances of an SNP file
but
it
fails when sending an RTF
Many thanks for any help or hints you may have. I can work out most
thing
so if you just have any pointers (going in the right direction) that
would
help also

This is the basic code
Public Sub SendEmailAll_Click()
Dim varItem As Variant
For Each varItem In Me.CmbClientList.ItemsSelected
Me.txtContact1stName = CmbClientList.Column(6, varItem)
Me.txtClientID = CmbClientList.Column(0, varItem)
'THIS WORKS FINE'
DoCmd.CopyObject , ETC, ETC.
' THIS WORKS FINE '
Me.txtClientID = Me.CmbClientList.Column(0, varItem)
' THIS WORKS FINE '
DoCmd.OpenReport ETC ETC
'THIS DOES NOT WORK AFTER THE 1ST RECORD'
DoCmd.SendObject acReport, "Booking Details" & " " & Format(Date,
"short
date") & " " & CmbClientList.Column(1, varItem),
"RichTextFormat(*.rtf)",
"E
MAIL ADDRESS DELETED FOR THIS POST ", , "", "Bookings Summery",
"MESSAGE
TEXT
HERE", False, ""
' THIS WORKS FINE '
DoCmd.Close acReport ETC ETC
'THIS WORKS FINE '
DoCmd.DeleteObject acReport, ETC ETC
' THIS WORKS FINE '
Next varItem
' THIS WORKS FINE '
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")
' THIS WORKS FINE '
DoCmd.Quit
End Sub

This is the full code - you may spot an error that I've missed ??
Public Sub SendEmailAll_Click()
Dim varItem As Variant
For Each varItem In Me.CmbClientList.ItemsSelected
Me.txtContact1stName = CmbClientList.Column(6, varItem)
Me.txtClientID = CmbClientList.Column(0, varItem)
'Copy charity and rename it'
DoCmd.CopyObject , "Booking Details" & " " & Format(Date, "short date")
&
"
" & CmbClientList.Column(1, varItem), acReport, "repCharityReport"
'set value to filter the report'
Me.txtClientID = Me.CmbClientList.Column(0, varItem)
'open the new report'
DoCmd.OpenReport "Booking Details" & " " & Format(Date, "short date") &
"
"
& CmbClientList.Column(1, varItem), acViewPreview, "",
"[DCCharityID]=[Forms]![frmReportSelector]![text34]", acHidden
'send the new report to the client'
DoCmd.SendObject acReport, "Booking Details" & " " & Format(Date,
"short
date") & " " & CmbClientList.Column(1, varItem),
"RichTextFormat(*.rtf)",
"E
MAIL ADDRESS DELETED FOR THIS POST", , "", "Bookings Summery", "New
format
automated report" & Chr(13) + Chr(10) & String(70, "_") _
& Chr(13) + Chr(10) & Chr(13) + Chr(10) & "Hi " & [txtContact1stName] &
Chr(13) + Chr(10) & Chr(13) + Chr(10) & "I have attached a report
showing
details of your current bookings." & Chr(13) + Chr(10) & Chr(13) +
Chr(10)
&
"The report gives details up to " & Format(Now, "dd mmmm yyyy hh:nn
AM/PM") &
"." & Chr(13) + Chr(10) & Chr(13) + Chr(10) & "Please note this report
is
fully automated; please ensure your IT dept. adds our IP address ( IP
ADDRESS
REMOVED FOR THIS POST ) and E Mail address to your white list." &
Chr(13)
+
Chr(10) & Chr(13) + Chr(10) & "If there are any problems with this
report
please contact me." & Chr(13) + Chr(10) & Chr(13) + Chr(10) & "SOME
NAME
HERE" & Chr(13) + Chr(10) & "TELEPHONE NUMBER REMOVED FOR THIS POST",
False,
""
'close the new report'
DoCmd.Close acReport, "Booking Details" & " " & Format(Date, "short
date")
&
" " & CmbClientList.Column(1, varItem)
'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "Booking Details" & " " & Format(Date,
"short
date") & " " & CmbClientList.Column(1, varItem)
'go to next item on the list and do it again'
Next varItem
'Open Outlook so the mails will send - outlook has a close action after
the
send'
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")
'close access'
DoCmd.Quit
 
A

aaron.kempf is a robot

CAUTION!
It has been determined that the message below was generated
programmatically, by a program robot ("bot") which also allows manual
intervention. This robot was created by a person or persons deliberately
trying to interfere with users of the Microsoft Access database software, and
uses the name of an actual person. As it is not possible to block posting by
this "bot", we post these cautions lest you be misled into taking the posts
as authentic and serious.
 
A

aaron.kempf is a robot

CAUTION!
It has been determined that the message below was generated
programmatically, by a program robot ("bot") which also allows manual
intervention. This robot was created by a person or persons deliberately
trying to interfere with users of the Microsoft Access database software, and
uses the name of an actual person. As it is not possible to block posting by
this "bot", we post these cautions lest you be misled into taking the posts
as authentic and serious.
 
Ad

Advertisements

T

Tony Toews [MVP]

bcap said:
You can do this natively in Access 2007, or in earlier versions you
can use Stephen Leban's free code (Google him) or a shareware product such
as Win2PDF.

While I haven't tried it I would think Leban's solution works fine in
A2007 as well.

Alternatively there is free, open source PDFCreator. (Watch out for
the *rsehole $$$ software with the same name.)
http://sourceforge.net/projects/pdfcreator/ Take a look at the VB
Script options in the C:\Program Files\PDFCreator\Scripts folder.
Figure out which one you need. With a bit of work you can convert the
VBScript code to VBA code.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
B

bcap

Clearly you have never tried sending an SNP file to someone whose computer's
default printer is set to a different paper size to the one on which you
created the SNP file.

Don't bother replying with your usual stream of juvenile abuse, the OP is
happy so I'm done with this thread.


message
nothing wrong with SNP.. except for the fact that MS didn't apply the
same concept to Excel

Basically-- we've had FOUR different 'portable document formats'
jammed down our throat in the past decade

SNP -> MDI -> XPS -> PDF

WTF?

I think that SNP rocks. Excel didn't have a portable document format
for FIVE YEARS after Access did.
And sending spreadsheets? There are MAJOR security problems with that
as well





Hi again,

I certainly didn't and wouldn't suggest SNP, it is a truly gruesome
format.

If you intend to distribute your application, be prepared for SendObject
to
work on some computers but (mysteriously and inexplicably) not work on
others.


Thanks for the feeback
I don't want to send out SNP files. I was just trying work out why SNP
format works fine and RTF does not.
Probelm solved - I gave up - am sending out excel files now.
I am (slowly) creating a completly new DB in 2007 vista. The sendobject
is
not the best but it's simple to use and (once you get it right ???)
tends
not
to go wrong and I have one frontend with the clickyes so this works fine
The problem I have, is the same as most people on this forum is that
every
department wants thier own "stuff" and trying to keep all depts is, to
say
the least, difficult.
The new DB will send as PDF format - but trying to keep clients happy is
what we do
SendObject is a law unto itself and deeply unreliable. I'd find another
way
of sending your emails if I were you. Personally I like CDOSYS (lots of
examples if you Google it).
Are you happy with the formatting of your Access reports converted to
RTF?
Personally I've always found this to be very unsatisfactory. PDF is
much
better. You can do this natively in Access 2007, or in earlier versions
you
can use Stephen Leban's free code (Google him) or a shareware product
such
as Win2PDF.
Hi all
I have a public function that is called from a small (very simple)
module.
The code fails after the 1st var instance on the send object. That is
it
will send the first report then copy the second but then will not
send
it
if
the (2nd) report is formated RTF.
I get an error message
2046 the Send Object method is not available now.
This is not an Outlook problem as the send object works fine if I
change
the
format to SNP - at the moment it is RTF. So the problem must be in
the
access vba.
I have spent 2 days on this (mad 2 lines of code) trying everything I
can
think of to find out why this would be - the keys on my keyboard that
spell
google are now worn out :)
Has anyone an idea why you can send multiple instances of an SNP file
but
it
fails when sending an RTF
Many thanks for any help or hints you may have. I can work out most
thing
so if you just have any pointers (going in the right direction) that
would
help also
________________________________________
This is the basic code
Public Sub SendEmailAll_Click()
Dim varItem As Variant
For Each varItem In Me.CmbClientList.ItemsSelected
Me.txtContact1stName = CmbClientList.Column(6, varItem)
Me.txtClientID = CmbClientList.Column(0, varItem)
'THIS WORKS FINE'
DoCmd.CopyObject , ETC, ETC.
' THIS WORKS FINE '
Me.txtClientID = Me.CmbClientList.Column(0, varItem)
' THIS WORKS FINE '
DoCmd.OpenReport ETC ETC
'THIS DOES NOT WORK AFTER THE 1ST RECORD'
DoCmd.SendObject acReport, "Booking Details" & " " & Format(Date,
"short
date") & " " & CmbClientList.Column(1, varItem),
"RichTextFormat(*.rtf)",
"E
MAIL ADDRESS DELETED FOR THIS POST ", , "", "Bookings Summery",
"MESSAGE
TEXT
HERE", False, ""
' THIS WORKS FINE '
DoCmd.Close acReport ETC ETC
'THIS WORKS FINE '
DoCmd.DeleteObject acReport, ETC ETC
' THIS WORKS FINE '
Next varItem
' THIS WORKS FINE '
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")
' THIS WORKS FINE '
DoCmd.Quit
End Sub
________________________________________
This is the full code - you may spot an error that I've missed ??
Public Sub SendEmailAll_Click()
Dim varItem As Variant
For Each varItem In Me.CmbClientList.ItemsSelected
Me.txtContact1stName = CmbClientList.Column(6, varItem)
Me.txtClientID = CmbClientList.Column(0, varItem)
'Copy charity and rename it'
DoCmd.CopyObject , "Booking Details" & " " & Format(Date, "short
date")
&
"
" & CmbClientList.Column(1, varItem), acReport, "repCharityReport"
'set value to filter the report'
Me.txtClientID = Me.CmbClientList.Column(0, varItem)
'open the new report'
DoCmd.OpenReport "Booking Details" & " " & Format(Date, "short date")
&
"
"
& CmbClientList.Column(1, varItem), acViewPreview, "",
"[DCCharityID]=[Forms]![frmReportSelector]![text34]", acHidden
'send the new report to the client'
DoCmd.SendObject acReport, "Booking Details" & " " & Format(Date,
"short
date") & " " & CmbClientList.Column(1, varItem),
"RichTextFormat(*.rtf)",
"E
MAIL ADDRESS DELETED FOR THIS POST", , "", "Bookings Summery", "New
format
automated report" & Chr(13) + Chr(10) & String(70, "_") _
& Chr(13) + Chr(10) & Chr(13) + Chr(10) & "Hi " & [txtContact1stName]
&
Chr(13) + Chr(10) & Chr(13) + Chr(10) & "I have attached a report
showing
details of your current bookings." & Chr(13) + Chr(10) & Chr(13) +
Chr(10)
&
"The report gives details up to " & Format(Now, "dd mmmm yyyy hh:nn
AM/PM") &
"." & Chr(13) + Chr(10) & Chr(13) + Chr(10) & "Please note this
report
is
fully automated; please ensure your IT dept. adds our IP address ( IP
ADDRESS
REMOVED FOR THIS POST ) and E Mail address to your white list." &
Chr(13)
+
Chr(10) & Chr(13) + Chr(10) & "If there are any problems with this
report
please contact me." & Chr(13) + Chr(10) & Chr(13) + Chr(10) & "SOME
NAME
HERE" & Chr(13) + Chr(10) & "TELEPHONE NUMBER REMOVED FOR THIS POST",
False,
""
'close the new report'
DoCmd.Close acReport, "Booking Details" & " " & Format(Date, "short
date")
&
" " & CmbClientList.Column(1, varItem)
'Deletes the new report - but leave the original template in place'
DoCmd.DeleteObject acReport, "Booking Details" & " " & Format(Date,
"short
date") & " " & CmbClientList.Column(1, varItem)
'go to next item on the list and do it again'
Next varItem
'Open Outlook so the mails will send - outlook has a close action
after
the
send'
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")
'close access'
DoCmd.Quit
End Sub
 
B

bcap

Tony Toews said:
While I haven't tried it I would think Leban's solution works fine in
A2007 as well.

I wan't trying to imply that Lebans' solution wouldn't work in A2007 (I
haven't tried it), I was only trying to say that for earlier versions you
*must* use Lebans or some other 3rd party solution.

However, given that the "native" support for PDF's in A2007 works just fine
and is much easier to implement than any other solution I've seen (except
for the annoyance of underlines disappearing from the output) I can't
imagine why anyone would want to use Lebans or any other 3rd party solution
with A2007 (unless merely upgrading an existing app to A2007, of course).
 
T

Tony Toews [MVP]

bcap said:
I wan't trying to imply that Lebans' solution wouldn't work in A2007 (I
haven't tried it), I was only trying to say that for earlier versions you
*must* use Lebans or some other 3rd party solution.

However, given that the "native" support for PDF's in A2007 works just fine
and is much easier to implement than any other solution I've seen (except
for the annoyance of underlines disappearing from the output) I can't
imagine why anyone would want to use Lebans or any other 3rd party solution
with A2007 (unless merely upgrading an existing app to A2007, of course).

IIRC the PDF option in A2007 is a separate download so right there may
be a problem if not installed and user isn't an administrator.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Ad

Advertisements

B

bcap

Tony Toews said:
IIRC the PDF option in A2007 is a separate download so right there may
be a problem if not installed and user isn't an administrator.

Tony

Well yes, that's true, but any organisation which makes it that difficult
for someone to carry out and use an "official" Microsoft Office download has
more than just technical problems!
 
A

a a r o n . k e m p f

SQL Server is an optional component on the Office 2000, 2002 and 2003
disk
SQL Server is an optional component on the Office 2000, 2002 and 2003
disk
SQL Server is an optional component on the Office 2000, 2002 and 2003
disk
 
T

Tony Toews [MVP]

bcap said:
Well yes, that's true, but any organisation which makes it that difficult
for someone to carry out and use an "official" Microsoft Office download has
more than just technical problems!

Agreed. But alternatives to ****ing around with IT for a month or two
can be quite useful. (Insert word of choice for ****. <smile>)

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top