Excel data exported to custom Outlook form
 

Greetings earthlings,

I have an issue that I have been trying to resolve for sometime, I think I
will provide the scenario and hope someone has an answer. Here it is.

- I have Excel workbook called "users.xls"
- I have a worksheet called "location"
- I have a cell B10 in the worksheet who's contents I want to be exported to
a textbox called "state" in a custom Outlook form called "RFCO" that resides
in the personal forms library in Outlook. (this custom form is from an
email template)
- I then want to be able to email the form.

How do I copy the contents of cell "B10" from worksheet "location" in
workbook "users.xls" to the custom Outlook form called "RFCO" and then
email? Thanks for the help

-lumpjaw

Excel data exported to custom Outlook form
 

It's not clear that what you want to do is even possible. Consider these issues:

1) A form contains no data. A form is a code/UI template that Outlook uses to display and run code for individual items. Therefore, it is not possible to export data to a form.

2) A text box itself contains no data. A text box is a control that can display either data from an Outlook property or data that the user or code puts into the control. Unless the text box is bound to an OUtlook property, the data in the text box is discarded when the item closes or sends.

3) Assuming we can figure out what you really have in mind with regard to #1 and #2, ***when*** do you want this "export" to take place?

4) Who are you mailing the item created by the form (note: you're not mailing form) to? Do they have access to the published form?

In the meantime, while you're pondering these issues, you might want to brush up on Excel automation techniques. The code samples using Excel listed at http://www.outlookcode.com/d/customimport.htm#samples might be a good place to start. I have a feeling that the Outlook item and control syntax information at http://www.outlookcode.com/d/propsyntax.htm will also come in handy.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"Lumpjaw" wrote in message ...
Greetings earthlings,

I have an issue that I have been trying to resolve for sometime, I think I
will provide the scenario and hope someone has an answer. Here it is.

- I have Excel workbook called "users.xls"
- I have a worksheet called "location"
- I have a cell B10 in the worksheet who's contents I want to be exported to
a textbox called "state" in a custom Outlook form called "RFCO" that resides
in the personal forms library in Outlook. (this custom form is from an
email template)
- I then want to be able to email the form.

How do I copy the contents of cell "B10" from worksheet "location" in
workbook "users.xls" to the custom Outlook form called "RFCO" and then
email? Thanks for the help

-lumpjaw

Excel data exported to custom Outlook form
 

Hi Sue,

What I want to do when I update the Excel spreadsheet is to run some code to
determine the pas due 'in days' for a person, if the days exceed 30days,
then i want to take the value in B10, which is currency data and put it into
a custom form and email it to the customer, i want this to happen when i
close the excel spreadsheet. Can it be done? Thanks

-lumpjaw


"Sue Mosher [MVP-Outlook]" wrote in message
...
It's not clear that what you want to do is even possible. Consider these
issues:

1) A form contains no data. A form is a code/UI template that Outlook uses
to display and run code for individual items. Therefore, it is not possible
to export data to a form.

2) A text box itself contains no data. A text box is a control that can
display either data from an Outlook property or data that the user or code
puts into the control. Unless the text box is bound to an OUtlook property,
the data in the text box is discarded when the item closes or sends.

3) Assuming we can figure out what you really have in mind with regard to #1
and #2, ***when*** do you want this "export" to take place?

4) Who are you mailing the item created by the form (note: you're not
mailing form) to? Do they have access to the published form?

In the meantime, while you're pondering these issues, you might want to
brush up on Excel automation techniques. The code samples using Excel listed
at http://www.outlookcode.com/d/customimport.htm#samples might be a good
place to start. I have a feeling that the Outlook item and control syntax
information at http://www.outlookcode.com/d/propsyntax.htm will also come in
handy.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"Lumpjaw" wrote in message
...
Greetings earthlings,

I have an issue that I have been trying to resolve for sometime, I think I
will provide the scenario and hope someone has an answer. Here it is.

- I have Excel workbook called "users.xls"
- I have a worksheet called "location"
- I have a cell B10 in the worksheet who's contents I want to be exported
to
a textbox called "state" in a custom Outlook form called "RFCO" that
resides
in the personal forms library in Outlook. (this custom form is from an
email template)
- I then want to be able to email the form.

How do I copy the contents of cell "B10" from worksheet "location" in
workbook "users.xls" to the custom Outlook form called "RFCO" and then
email? Thanks for the help

-lumpjaw

Excel data exported to custom Outlook form
 

How would i open a form (email) from within Excel? Thanks


"Sue Mosher [MVP-Outlook]" wrote in message
...
It's not clear that what you want to do is even possible. Consider these
issues:

1) A form contains no data. A form is a code/UI template that Outlook uses
to display and run code for individual items. Therefore, it is not possible
to export data to a form.

2) A text box itself contains no data. A text box is a control that can
display either data from an Outlook property or data that the user or code
puts into the control. Unless the text box is bound to an OUtlook property,
the data in the text box is discarded when the item closes or sends.

3) Assuming we can figure out what you really have in mind with regard to #1
and #2, ***when*** do you want this "export" to take place?

4) Who are you mailing the item created by the form (note: you're not
mailing form) to? Do they have access to the published form?

In the meantime, while you're pondering these issues, you might want to
brush up on Excel automation techniques. The code samples using Excel listed
at http://www.outlookcode.com/d/customimport.htm#samples might be a good
place to start. I have a feeling that the Outlook item and control syntax
information at http://www.outlookcode.com/d/propsyntax.htm will also come in
handy.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"Lumpjaw" wrote in message
...
Greetings earthlings,

I have an issue that I have been trying to resolve for sometime, I think I
will provide the scenario and hope someone has an answer. Here it is.

- I have Excel workbook called "users.xls"
- I have a worksheet called "location"
- I have a cell B10 in the worksheet who's contents I want to be exported
to
a textbox called "state" in a custom Outlook form called "RFCO" that
resides
in the personal forms library in Outlook. (this custom form is from an
email template)
- I then want to be able to email the form.

How do I copy the contents of cell "B10" from worksheet "location" in
workbook "users.xls" to the custom Outlook form called "RFCO" and then
email? Thanks for the help

-lumpjaw

Excel data exported to custom Outlook form
 

Email it to a customer? You can't count on being able to do that with a custom form unless the customer has not just Outlook but also access to the published form definition. I'd suggest that you put code in your spreadsheet's VBA module to simply create a new email message (the Outlook object model method for that is Application.CreateItem) and then put the information the customer needs in the message's Body property.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"Lumpjaw" wrote in message ...
Hi Sue,

What I want to do when I update the Excel spreadsheet is to run some code to
determine the pas due 'in days' for a person, if the days exceed 30days,
then i want to take the value in B10, which is currency data and put it into
a custom form and email it to the customer, i want this to happen when i
close the excel spreadsheet. Can it be done? Thanks

-lumpjaw


"Sue Mosher [MVP-Outlook]" wrote in message
...
It's not clear that what you want to do is even possible. Consider these
issues:

1) A form contains no data. A form is a code/UI template that Outlook uses
to display and run code for individual items. Therefore, it is not possible
to export data to a form.

2) A text box itself contains no data. A text box is a control that can
display either data from an Outlook property or data that the user or code
puts into the control. Unless the text box is bound to an OUtlook property,
the data in the text box is discarded when the item closes or sends.

3) Assuming we can figure out what you really have in mind with regard to #1
and #2, ***when*** do you want this "export" to take place?

4) Who are you mailing the item created by the form (note: you're not
mailing form) to? Do they have access to the published form?

In the meantime, while you're pondering these issues, you might want to
brush up on Excel automation techniques. The code samples using Excel listed
at http://www.outlookcode.com/d/customimport.htm#samples might be a good
place to start. I have a feeling that the Outlook item and control syntax
information at http://www.outlookcode.com/d/propsyntax.htm will also come in
handy.

"Lumpjaw" wrote in message
...
Greetings earthlings,

I have an issue that I have been trying to resolve for sometime, I think I
will provide the scenario and hope someone has an answer. Here it is.

- I have Excel workbook called "users.xls"
- I have a worksheet called "location"
- I have a cell B10 in the worksheet who's contents I want to be exported
to
a textbox called "state" in a custom Outlook form called "RFCO" that
resides
in the personal forms library in Outlook. (this custom form is from an
email template)
- I then want to be able to email the form.

How do I copy the contents of cell "B10" from worksheet "location" in
workbook "users.xls" to the custom Outlook form called "RFCO" and then
email? Thanks for the help

-lumpjaw

Excel data exported to custom Outlook form
 

Do you mean create a new message from a published custom form? You'd add a reference to Outlook to your VBA project so you can use Outlook object model methods as at http://www.outlookcode.com/d/launchform.htm

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"Lumpjaw" wrote in message ...
How would i open a form (email) from within Excel? Thanks

Excel data exported to custom Outlook form
 

Hi Susan,

I know that when I designed the form, I had the box checked for transmitting
the form defs. so that should not be a problem. But in the event it is...
how would the code look to send an email from within Excel to include the
contents of B10 within the body, i.e....



"Dear Mr. so and so,


You monthly bill of <b10 contents is past due. Thank you."




How can I do this? Thank You.

-Lumpjaw



"Sue Mosher [MVP-Outlook]" wrote in message
...
Email it to a customer? You can't count on being able to do that with a
custom form unless the customer has not just Outlook but also access to the
published form definition. I'd suggest that you put code in your
spreadsheet's VBA module to simply create a new email message (the Outlook
object model method for that is Application.CreateItem) and then put the
information the customer needs in the message's Body property.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"Lumpjaw" wrote in message
...
Hi Sue,

What I want to do when I update the Excel spreadsheet is to run some code
to
determine the pas due 'in days' for a person, if the days exceed 30days,
then i want to take the value in B10, which is currency data and put it
into
a custom form and email it to the customer, i want this to happen when i
close the excel spreadsheet. Can it be done? Thanks

-lumpjaw


"Sue Mosher [MVP-Outlook]" wrote in message
...
It's not clear that what you want to do is even possible. Consider these
issues:

1) A form contains no data. A form is a code/UI template that Outlook uses
to display and run code for individual items. Therefore, it is not
possible
to export data to a form.

2) A text box itself contains no data. A text box is a control that can
display either data from an Outlook property or data that the user or code
puts into the control. Unless the text box is bound to an OUtlook
property,
the data in the text box is discarded when the item closes or sends.

3) Assuming we can figure out what you really have in mind with regard to
#1
and #2, ***when*** do you want this "export" to take place?

4) Who are you mailing the item created by the form (note: you're not
mailing form) to? Do they have access to the published form?

In the meantime, while you're pondering these issues, you might want to
brush up on Excel automation techniques. The code samples using Excel
listed
at http://www.outlookcode.com/d/customimport.htm#samples might be a good
place to start. I have a feeling that the Outlook item and control syntax
information at http://www.outlookcode.com/d/propsyntax.htm will also come
in
handy.

"Lumpjaw" wrote in message
...
Greetings earthlings,

I have an issue that I have been trying to resolve for sometime, I think
I
will provide the scenario and hope someone has an answer. Here it is.

- I have Excel workbook called "users.xls"
- I have a worksheet called "location"
- I have a cell B10 in the worksheet who's contents I want to be exported
to
a textbox called "state" in a custom Outlook form called "RFCO" that
resides
in the personal forms library in Outlook. (this custom form is from
an
email template)
- I then want to be able to email the form.

How do I copy the contents of cell "B10" from worksheet "location" in
workbook "users.xls" to the custom Outlook form called "RFCO" and then
email? Thanks for the help

-lumpjaw

Excel data exported to custom Outlook form
 

I know that when I designed the form, I had the box checked for transmitting
the form defs. so that should not be a problem.

Actually, that is exactly the problem if you're trying to send a custom form. In recent versions, it makes any custom fields unreadable.

how would the code look to send an email from within Excel to include the
contents of B10 within the body, i.e....

Sub makemsg()
' need reference to Microsoft Outlook library
Dim ol As Outlook.Application
Dim msg As Outlook.MailItem
'On Error Resume Next
Set ol = OpenOL()
Set msg = ol.CreateItem(olMailItem)
msg.Body = "some text " & _
ActiveSheet.Range("B10").Value & _
" more text"
msg.Display
Set msg = Nothing
Set ol = Nothing
End Sub

Function OpenOL(Optional ProfileName) As Outlook.Application
Dim objOL As Outlook.Application
On Error Resume Next
Set objOL = GetObject(, "Outlook.Application")
If objOL Is Nothing Then
Set objOL = CreateObject("Outlook.Application")
objOL.Session.Logon ProfileName, , False, True
End If
Set OpenOL = objOL
Set objOL = Nothing
End Function

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx


"Sue Mosher [MVP-Outlook]" wrote in message
...
Email it to a customer? You can't count on being able to do that with a
custom form unless the customer has not just Outlook but also access to the
published form definition. I'd suggest that you put code in your
spreadsheet's VBA module to simply create a new email message (the Outlook
object model method for that is Application.CreateItem) and then put the
information the customer needs in the message's Body property.

"Lumpjaw" wrote in message
...
Hi Sue,

What I want to do when I update the Excel spreadsheet is to run some code
to
determine the pas due 'in days' for a person, if the days exceed 30days,
then i want to take the value in B10, which is currency data and put it
into
a custom form and email it to the customer, i want this to happen when i
close the excel spreadsheet. Can it be done? Thanks

-lumpjaw


"Sue Mosher [MVP-Outlook]" wrote in message
...
It's not clear that what you want to do is even possible. Consider these
issues:

1) A form contains no data. A form is a code/UI template that Outlook uses
to display and run code for individual items. Therefore, it is not
possible
to export data to a form.

2) A text box itself contains no data. A text box is a control that can
display either data from an Outlook property or data that the user or code
puts into the control. Unless the text box is bound to an OUtlook
property,
the data in the text box is discarded when the item closes or sends.

3) Assuming we can figure out what you really have in mind with regard to
#1
and #2, ***when*** do you want this "export" to take place?

4) Who are you mailing the item created by the form (note: you're not
mailing form) to? Do they have access to the published form?

In the meantime, while you're pondering these issues, you might want to
brush up on Excel automation techniques. The code samples using Excel
listed
at http://www.outlookcode.com/d/customimport.htm#samples might be a good
place to start. I have a feeling that the Outlook item and control syntax
information at http://www.outlookcode.com/d/propsyntax.htm will also come
in
handy.

"Lumpjaw" wrote in message
...
Greetings earthlings,

I have an issue that I have been trying to resolve for sometime, I think
I
will provide the scenario and hope someone has an answer. Here it is.

- I have Excel workbook called "users.xls"
- I have a worksheet called "location"
- I have a cell B10 in the worksheet who's contents I want to be exported
to
a textbox called "state" in a custom Outlook form called "RFCO" that
resides
in the personal forms library in Outlook. (this custom form is from
an
email template)
- I then want to be able to email the form.

How do I copy the contents of cell "B10" from worksheet "location" in
workbook "users.xls" to the custom Outlook form called "RFCO" and then
email? Thanks for the help

Excel data exported to custom Outlook form
 

Thank you.

-lumpjaw

"Sue Mosher [MVP-Outlook]" wrote in message
...
I know that when I designed the form, I had the box checked for
transmitting
the form defs. so that should not be a problem.

Actually, that is exactly the problem if you're trying to send a custom
form. In recent versions, it makes any custom fields unreadable.

how would the code look to send an email from within Excel to include the
contents of B10 within the body, i.e....

Sub makemsg()
' need reference to Microsoft Outlook library
Dim ol As Outlook.Application
Dim msg As Outlook.MailItem
'On Error Resume Next
Set ol = OpenOL()
Set msg = ol.CreateItem(olMailItem)
msg.Body = "some text " & _
ActiveSheet.Range("B10").Value & _
" more text"
msg.Display
Set msg = Nothing
Set ol = Nothing
End Sub

Function OpenOL(Optional ProfileName) As Outlook.Application
Dim objOL As Outlook.Application
On Error Resume Next
Set objOL = GetObject(, "Outlook.Application")
If objOL Is Nothing Then
Set objOL = CreateObject("Outlook.Application")
objOL.Session.Logon ProfileName, , False, True
End If
Set OpenOL = objOL
Set objOL = Nothing
End Function

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx


"Sue Mosher [MVP-Outlook]" wrote in message
...
Email it to a customer? You can't count on being able to do that with a
custom form unless the customer has not just Outlook but also access to
the
published form definition. I'd suggest that you put code in your
spreadsheet's VBA module to simply create a new email message (the Outlook
object model method for that is Application.CreateItem) and then put the
information the customer needs in the message's Body property.

"Lumpjaw" wrote in message
...
Hi Sue,

What I want to do when I update the Excel spreadsheet is to run some code
to
determine the pas due 'in days' for a person, if the days exceed 30days,
then i want to take the value in B10, which is currency data and put it
into
a custom form and email it to the customer, i want this to happen when i
close the excel spreadsheet. Can it be done? Thanks

-lumpjaw


"Sue Mosher [MVP-Outlook]" wrote in message
...
It's not clear that what you want to do is even possible. Consider these
issues:

1) A form contains no data. A form is a code/UI template that Outlook
uses
to display and run code for individual items. Therefore, it is not
possible
to export data to a form.

2) A text box itself contains no data. A text box is a control that can
display either data from an Outlook property or data that the user or
code
puts into the control. Unless the text box is bound to an OUtlook
property,
the data in the text box is discarded when the item closes or sends.

3) Assuming we can figure out what you really have in mind with regard to
#1
and #2, ***when*** do you want this "export" to take place?

4) Who are you mailing the item created by the form (note: you're not
mailing form) to? Do they have access to the published form?

In the meantime, while you're pondering these issues, you might want to
brush up on Excel automation techniques. The code samples using Excel
listed
at http://www.outlookcode.com/d/customimport.htm#samples might be a good
place to start. I have a feeling that the Outlook item and control syntax
information at http://www.outlookcode.com/d/propsyntax.htm will also come
in
handy.

"Lumpjaw" wrote in message
...
Greetings earthlings,

I have an issue that I have been trying to resolve for sometime, I think
I
will provide the scenario and hope someone has an answer. Here it is.

- I have Excel workbook called "users.xls"
- I have a worksheet called "location"
- I have a cell B10 in the worksheet who's contents I want to be
exported
to
a textbox called "state" in a custom Outlook form called "RFCO" that
resides
in the personal forms library in Outlook. (this custom form is from
an
email template)
- I then want to be able to email the form.

How do I copy the contents of cell "B10" from worksheet "location" in
workbook "users.xls" to the custom Outlook form called "RFCO" and then
email? Thanks for the help

Excel data exported to custom Outlook form
 

Hi sue,

I experimented with the code this weekend and could not get it to work.
Maybe it is a problem with where I am putting it. Could you lend a littl
advice? Thanks.

"Sue Mosher [MVP-Outlook]" wrote:

I know that when I designed the form, I had the box checked for transmitting
the form defs. so that should not be a problem.

Actually, that is exactly the problem if you're trying to send a custom form. In recent versions, it makes any custom fields unreadable.

how would the code look to send an email from within Excel to include the
contents of B10 within the body, i.e....

Sub makemsg()
' need reference to Microsoft Outlook library
Dim ol As Outlook.Application
Dim msg As Outlook.MailItem
'On Error Resume Next
Set ol = OpenOL()
Set msg = ol.CreateItem(olMailItem)
msg.Body = "some text " & _
ActiveSheet.Range("B10").Value & _
" more text"
msg.Display
Set msg = Nothing
Set ol = Nothing
End Sub

Function OpenOL(Optional ProfileName) As Outlook.Application
Dim objOL As Outlook.Application
On Error Resume Next
Set objOL = GetObject(, "Outlook.Application")
If objOL Is Nothing Then
Set objOL = CreateObject("Outlook.Application")
objOL.Session.Logon ProfileName, , False, True
End If
Set OpenOL = objOL
Set objOL = Nothing
End Function

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx


"Sue Mosher [MVP-Outlook]" wrote in message
...
Email it to a customer? You can't count on being able to do that with a
custom form unless the customer has not just Outlook but also access to the
published form definition. I'd suggest that you put code in your
spreadsheet's VBA module to simply create a new email message (the Outlook
object model method for that is Application.CreateItem) and then put the
information the customer needs in the message's Body property.

"Lumpjaw" wrote in message
...
Hi Sue,

What I want to do when I update the Excel spreadsheet is to run some code
to
determine the pas due 'in days' for a person, if the days exceed 30days,
then i want to take the value in B10, which is currency data and put it
into
a custom form and email it to the customer, i want this to happen when i
close the excel spreadsheet. Can it be done? Thanks

-lumpjaw


"Sue Mosher [MVP-Outlook]" wrote in message
...
It's not clear that what you want to do is even possible. Consider these
issues:

1) A form contains no data. A form is a code/UI template that Outlook uses
to display and run code for individual items. Therefore, it is not
possible
to export data to a form.

2) A text box itself contains no data. A text box is a control that can
display either data from an Outlook property or data that the user or code
puts into the control. Unless the text box is bound to an OUtlook
property,
the data in the text box is discarded when the item closes or sends.

3) Assuming we can figure out what you really have in mind with regard to
#1
and #2, ***when*** do you want this "export" to take place?

4) Who are you mailing the item created by the form (note: you're not
mailing form) to? Do they have access to the published form?

In the meantime, while you're pondering these issues, you might want to
brush up on Excel automation techniques. The code samples using Excel
listed
at http://www.outlookcode.com/d/customimport.htm#samples might be a good
place to start. I have a feeling that the Outlook item and control syntax
information at http://www.outlookcode.com/d/propsyntax.htm will also come
in
handy.

"Lumpjaw" wrote in message
...
Greetings earthlings,

I have an issue that I have been trying to resolve for sometime, I think
I
will provide the scenario and hope someone has an answer. Here it is.

- I have Excel workbook called "users.xls"
- I have a worksheet called "location"
- I have a cell B10 in the worksheet who's contents I want to be exported
to
a textbox called "state" in a custom Outlook form called "RFCO" that
resides
in the personal forms library in Outlook. (this custom form is from
an
email template)
- I then want to be able to email the form.

How do I copy the contents of cell "B10" from worksheet "location" in
workbook "users.xls" to the custom Outlook form called "RFCO" and then
email? Thanks for the help

Excel data exported to custom Outlook form
 

1) Comment out the other On Error Resume Next statement and report any error messages.

2) Step through the code statement by statement in the debugger to see if it executes as you expect it to.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"Lumpjaw" wrote in message ...
Hi sue,

I experimented with the code this weekend and could not get it to work.
Maybe it is a problem with where I am putting it. Could you lend a littl
advice? Thanks.

"Sue Mosher [MVP-Outlook]" wrote:

I know that when I designed the form, I had the box checked for transmitting
the form defs. so that should not be a problem.

Actually, that is exactly the problem if you're trying to send a custom form. In recent versions, it makes any custom fields unreadable.

how would the code look to send an email from within Excel to include the
contents of B10 within the body, i.e....

Sub makemsg()
' need reference to Microsoft Outlook library
Dim ol As Outlook.Application
Dim msg As Outlook.MailItem
'On Error Resume Next
Set ol = OpenOL()
Set msg = ol.CreateItem(olMailItem)
msg.Body = "some text " & _
ActiveSheet.Range("B10").Value & _
" more text"
msg.Display
Set msg = Nothing
Set ol = Nothing
End Sub

Function OpenOL(Optional ProfileName) As Outlook.Application
Dim objOL As Outlook.Application
On Error Resume Next
Set objOL = GetObject(, "Outlook.Application")
If objOL Is Nothing Then
Set objOL = CreateObject("Outlook.Application")
objOL.Session.Logon ProfileName, , False, True
End If
Set OpenOL = objOL
Set objOL = Nothing
End Function


"Sue Mosher [MVP-Outlook]" wrote in message
...
Email it to a customer? You can't count on being able to do that with a
custom form unless the customer has not just Outlook but also access to the
published form definition. I'd suggest that you put code in your
spreadsheet's VBA module to simply create a new email message (the Outlook
object model method for that is Application.CreateItem) and then put the
information the customer needs in the message's Body property.

"Lumpjaw" wrote in message
...
Hi Sue,

What I want to do when I update the Excel spreadsheet is to run some code
to
determine the pas due 'in days' for a person, if the days exceed 30days,
then i want to take the value in B10, which is currency data and put it
into
a custom form and email it to the customer, i want this to happen when i
close the excel spreadsheet. Can it be done? Thanks

-lumpjaw


"Sue Mosher [MVP-Outlook]" wrote in message
...
It's not clear that what you want to do is even possible. Consider these
issues:

1) A form contains no data. A form is a code/UI template that Outlook uses
to display and run code for individual items. Therefore, it is not
possible
to export data to a form.

2) A text box itself contains no data. A text box is a control that can
display either data from an Outlook property or data that the user or code
puts into the control. Unless the text box is bound to an OUtlook
property,
the data in the text box is discarded when the item closes or sends.

3) Assuming we can figure out what you really have in mind with regard to
#1
and #2, ***when*** do you want this "export" to take place?

4) Who are you mailing the item created by the form (note: you're not
mailing form) to? Do they have access to the published form?

In the meantime, while you're pondering these issues, you might want to
brush up on Excel automation techniques. The code samples using Excel
listed
at http://www.outlookcode.com/d/customimport.htm#samples might be a good
place to start. I have a feeling that the Outlook item and control syntax
information at http://www.outlookcode.com/d/propsyntax.htm will also come
in
handy.

"Lumpjaw" wrote in message
...
Greetings earthlings,

I have an issue that I have been trying to resolve for sometime, I think
I
will provide the scenario and hope someone has an answer. Here it is.

- I have Excel workbook called "users.xls"
- I have a worksheet called "location"
- I have a cell B10 in the worksheet who's contents I want to be exported
to
a textbox called "state" in a custom Outlook form called "RFCO" that
resides
in the personal forms library in Outlook. (this custom form is from
an
email template)
- I then want to be able to email the form.

How do I copy the contents of cell "B10" from worksheet "location" in
workbook "users.xls" to the custom Outlook form called "RFCO" and then
email? Thanks for the help

Excel data exported to custom Outlook form
 

Hi Sue, I tried what you suggested, this is my error


User-defined tye not defined on this line...


Function OpenOL(Optional ProfileName) As Outlook.Application <<

What do you think. Thanks.

"Sue Mosher [MVP-Outlook]" wrote:

1) Comment out the other On Error Resume Next statement and report any error messages.

2) Step through the code statement by statement in the debugger to see if it executes as you expect it to.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"Lumpjaw" wrote in message ...
Hi sue,

I experimented with the code this weekend and could not get it to work.
Maybe it is a problem with where I am putting it. Could you lend a littl
advice? Thanks.

"Sue Mosher [MVP-Outlook]" wrote:

I know that when I designed the form, I had the box checked for transmitting
the form defs. so that should not be a problem.

Actually, that is exactly the problem if you're trying to send a custom form. In recent versions, it makes any custom fields unreadable.

how would the code look to send an email from within Excel to include the
contents of B10 within the body, i.e....

Sub makemsg()
' need reference to Microsoft Outlook library
Dim ol As Outlook.Application
Dim msg As Outlook.MailItem
'On Error Resume Next
Set ol = OpenOL()
Set msg = ol.CreateItem(olMailItem)
msg.Body = "some text " & _
ActiveSheet.Range("B10").Value & _
" more text"
msg.Display
Set msg = Nothing
Set ol = Nothing
End Sub

Function OpenOL(Optional ProfileName) As Outlook.Application
Dim objOL As Outlook.Application
On Error Resume Next
Set objOL = GetObject(, "Outlook.Application")
If objOL Is Nothing Then
Set objOL = CreateObject("Outlook.Application")
objOL.Session.Logon ProfileName, , False, True
End If
Set OpenOL = objOL
Set objOL = Nothing
End Function


"Sue Mosher [MVP-Outlook]" wrote in message
...
Email it to a customer? You can't count on being able to do that with a
custom form unless the customer has not just Outlook but also access to the
published form definition. I'd suggest that you put code in your
spreadsheet's VBA module to simply create a new email message (the Outlook
object model method for that is Application.CreateItem) and then put the
information the customer needs in the message's Body property.

"Lumpjaw" wrote in message
...
Hi Sue,

What I want to do when I update the Excel spreadsheet is to run some code
to
determine the pas due 'in days' for a person, if the days exceed 30days,
then i want to take the value in B10, which is currency data and put it
into
a custom form and email it to the customer, i want this to happen when i
close the excel spreadsheet. Can it be done? Thanks

-lumpjaw


"Sue Mosher [MVP-Outlook]" wrote in message
...
It's not clear that what you want to do is even possible. Consider these
issues:

1) A form contains no data. A form is a code/UI template that Outlook uses
to display and run code for individual items. Therefore, it is not
possible
to export data to a form.

2) A text box itself contains no data. A text box is a control that can
display either data from an Outlook property or data that the user or code
puts into the control. Unless the text box is bound to an OUtlook
property,
the data in the text box is discarded when the item closes or sends.

3) Assuming we can figure out what you really have in mind with regard to
#1
and #2, ***when*** do you want this "export" to take place?

4) Who are you mailing the item created by the form (note: you're not
mailing form) to? Do they have access to the published form?

In the meantime, while you're pondering these issues, you might want to
brush up on Excel automation techniques. The code samples using Excel
listed
at http://www.outlookcode.com/d/customimport.htm#samples might be a good
place to start. I have a feeling that the Outlook item and control syntax
information at http://www.outlookcode.com/d/propsyntax.htm will also come
in
handy.

"Lumpjaw" wrote in message
...
Greetings earthlings,

I have an issue that I have been trying to resolve for sometime, I think
I
will provide the scenario and hope someone has an answer. Here it is.

- I have Excel workbook called "users.xls"
- I have a worksheet called "location"
- I have a cell B10 in the worksheet who's contents I want to be exported
to
a textbox called "state" in a custom Outlook form called "RFCO" that
resides
in the personal forms library in Outlook. (this custom form is from
an
email template)
- I then want to be able to email the form.

How do I copy the contents of cell "B10" from worksheet "location" in
workbook "users.xls" to the custom Outlook form called "RFCO" and then
email? Thanks for the help

Excel data exported to custom Outlook form
 

Hey Susan,

I did not have the Outlook references enabled, works great! Thanks.

-lumpjaw


"Sue Mosher [MVP-Outlook]" wrote in message
...
1) Comment out the other On Error Resume Next statement and report any error
messages.

2) Step through the code statement by statement in the debugger to see if it
executes as you expect it to.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"Lumpjaw" wrote in message
...
Hi sue,

I experimented with the code this weekend and could not get it to work.
Maybe it is a problem with where I am putting it. Could you lend a littl
advice? Thanks.

"Sue Mosher [MVP-Outlook]" wrote:

I know that when I designed the form, I had the box checked for
transmitting
the form defs. so that should not be a problem.

Actually, that is exactly the problem if you're trying to send a custom
form. In recent versions, it makes any custom fields unreadable.

how would the code look to send an email from within Excel to include
the
contents of B10 within the body, i.e....

Sub makemsg()
' need reference to Microsoft Outlook library
Dim ol As Outlook.Application
Dim msg As Outlook.MailItem
'On Error Resume Next
Set ol = OpenOL()
Set msg = ol.CreateItem(olMailItem)
msg.Body = "some text " & _
ActiveSheet.Range("B10").Value & _
" more text"
msg.Display
Set msg = Nothing
Set ol = Nothing
End Sub

Function OpenOL(Optional ProfileName) As Outlook.Application
Dim objOL As Outlook.Application
On Error Resume Next
Set objOL = GetObject(, "Outlook.Application")
If objOL Is Nothing Then
Set objOL = CreateObject("Outlook.Application")
objOL.Session.Logon ProfileName, , False, True
End If
Set OpenOL = objOL
Set objOL = Nothing
End Function


"Sue Mosher [MVP-Outlook]" wrote in message
...
Email it to a customer? You can't count on being able to do that with a
custom form unless the customer has not just Outlook but also access to
the
published form definition. I'd suggest that you put code in your
spreadsheet's VBA module to simply create a new email message (the
Outlook
object model method for that is Application.CreateItem) and then put
the
information the customer needs in the message's Body property.

"Lumpjaw" wrote in message
...
Hi Sue,

What I want to do when I update the Excel spreadsheet is to run some
code
to
determine the pas due 'in days' for a person, if the days exceed
30days,
then i want to take the value in B10, which is currency data and put
it
into
a custom form and email it to the customer, i want this to happen when
i
close the excel spreadsheet. Can it be done? Thanks

-lumpjaw


"Sue Mosher [MVP-Outlook]" wrote in message
...
It's not clear that what you want to do is even possible. Consider
these
issues:

1) A form contains no data. A form is a code/UI template that Outlook
uses
to display and run code for individual items. Therefore, it is not
possible
to export data to a form.

2) A text box itself contains no data. A text box is a control that
can
display either data from an Outlook property or data that the user or
code
puts into the control. Unless the text box is bound to an OUtlook
property,
the data in the text box is discarded when the item closes or sends.

3) Assuming we can figure out what you really have in mind with regard
to
#1
and #2, ***when*** do you want this "export" to take place?

4) Who are you mailing the item created by the form (note: you're not
mailing form) to? Do they have access to the published form?

In the meantime, while you're pondering these issues, you might want
to
brush up on Excel automation techniques. The code samples using Excel
listed
at http://www.outlookcode.com/d/customimport.htm#samples might be a
good
place to start. I have a feeling that the Outlook item and control
syntax
information at http://www.outlookcode.com/d/propsyntax.htm will also
come
in
handy.

"Lumpjaw" wrote in message
...
Greetings earthlings,

I have an issue that I have been trying to resolve for sometime, I
think
I
will provide the scenario and hope someone has an answer. Here it
is.

- I have Excel workbook called "users.xls"
- I have a worksheet called "location"
- I have a cell B10 in the worksheet who's contents I want to be
exported
to
a textbox called "state" in a custom Outlook form called "RFCO" that
resides
in the personal forms library in Outlook. (this custom form is
from
an
email template)
- I then want to be able to email the form.

How do I copy the contents of cell "B10" from worksheet "location" in
workbook "users.xls" to the custom Outlook form called "RFCO" and
then
email? Thanks for the help