Hi Everyone,
I have a huge problem here.I am not able to figure out how to do that.I have an outlook template in which I am embedding an excel worksheet as image and also the excel workbook itself as an attachment.I want to send this email template through excel so that everytime the excel updates both the attachment and the content in the email body changes and updated data is sent.Plz help.
Thanks.
Hi sqledge
This code should give you the basic "stuff" that's required. Place the code in a general module. If you need help applying to your needs, let me know.
Please Login or Register to view this content.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
Hi jaslake,
Thanks for the code.It helped me to get the template being sent along with attachment.But I need to copy range say "b7:q99" from sheet1 and embed it as a picture or bitmap onto the body of the template.The template already has some text in it.I want to embed this picture in the middle of the data.I mean suppose if I have 4 lines of text in template body,i want this image or picture to be embeded after the 2nd line.Your help would be highly appreciated.
Thanks
Hi sqledge
Attach a copy of your template (you'll have to zip it) and show me where to embed the image. A mock-up of your sheet1 will also be helpful.
Is the range of cellsALWAYS the same?say "b7:q99" from sheet1
I've not done this in the past (embedding in the middle)I'll need to "play" with it.suppose if I have 4 lines of text in template body,i want this image or picture to be embedded after the 2nd line.
You ARE using Excel 2007 and Outlook 2007 are you not?
John
Yes the range is fixed.
I use EXCEL and OUTLOOK 2010.
By middle i didn mean exactly in the middle.But after few lines in the template I need to embed this picture.
Sorry i could not attach the template as it belongs to my work.It is not allowed.
Hi sqledge
Dummy up a template...use your imagination to create something that will simulate your actual environment. Do the same for the worksheet you referenced. I'll be glad to look at it with you.
Why an image rather than the range contents itself?
John
Last edited by jaslake; 10-19-2010 at 05:38 PM.
It can be anything but my data has sparklines in one column.I thought that wud be a problem.I am attaching a sample template which looks xactly like the one i need.
What areNever heard of them.sparklines
Sparklines are just like bar graphs.It is used to see trend of data over a range or period.
Hi sqledge
The attached has this code in Module1and this code in Module2Please Login or Register to view this content.Please Login or Register to view this content.
I'm attaching two(2) files:Untitled.zip contains "Untitled.oft" which is the Outlook Template file.
- Example(5).xlsm
- Untitled.zip
For purposes of testing ""Example(5).xlsm" and "Untitled.oft" MUST be in the SAME folder. If they are, the procedure should run fine (this is for testing purposes only...they don't need to "live" in the same folder in your live environment.
When you unzip "Untitled.zip", "Untitled.oft" will probably not be in the correct folder. If not, either move or copy "Untitled.oft" to the correct folder.
I've modified your original template such that it now contains four tables whereas your original template contained one table. The distinction is important as the insertion point relies on the table index (index 3 versus index 1)
I've tested this extensively in Excel 2007 with Outlook 2007. I don't have 2010 (your versions) so I have no idea of the transition. I assume it should work.
These are difficult concepts...we may have several false starts getting your environment established. Once we do I believe the procedures do as you requested (formatting will ALWAYS be an issue)
Let me know of your issues.
John
Hi Jaslake,
I am unable to find the untitled.zip attachment with the previous post.Is it possible for you to repost the template?
Thanks
Hi sqledge
Sorry about that.
John
Hi John,
There is a small change.Now i need to insert a bmp image thats already on the desktop in that place where we are pasting the excel range.I have modified my template as you said into 4 tables.I didnt know exactly how to do that.So copied your template and modified the content in that.How can we achieve this?.How can we insert this image in that place?
Thanks
Hi sqledge
Try the attached. You'll need to change these two lines of code:andPlease Login or Register to view this content.Use the same template as before (untitled.oft).Please Login or Register to view this content.
You said:
I did this by copy/paste your original template into Word then used Word's table tools to insert tables where I wanted them. I then copy/pasted that document back to an Outlook Email and saved it as a new template (untitled.oft).I have modified my template as you said into 4 tables.I didn't know exactly how to do that.
Let me know of issues.
John
Hi John,
Thats a ton of help.Yeah tried that table stuff on word and came to know how that works.
I also want to know how can we change the dimensions of the picture that we are inserting
here coz this picture seems to be little and the numbers are not clear on that.So if we can enlarge the dimensions while inerting that will be perfect.Once again thanks a lot John this is such a big help.
Thanks,
kris
Hi kris
if you'll attach a sample picture, I'll play with it. Can't do much unless i know what you're working with.
John
PS: I'd recommend you change your profile to include Excel 2010. Meant to mention this previously.
Last edited by jaslake; 10-25-2010 at 05:20 PM.
Hi John,
While we are saving the picture it is getting saved as a very small picture than the actual size of the clipboard.So ,is there any way that we get the exact picture same as the size on original excel sheet?
Thanks in advance.
kris
Hi kris
Try the attached. It seems to do this for meAdjust this line of code to get the effect you wantenlarge the dimensions while insertingLet me know of issues.Please Login or Register to view this content.
John
Hi kris
Don't know. I'll need to play with it.is there any way that we get the exact picture same as the size on original excel sheet?
John
Hi kris
What code are you using to create the image file?
John
With Worksheets("sheet1")
Set rng = .Range("B7:Q99")
End With
rng.CopyPicture xlScreen, xlPicture
Set pic = ActiveSheet.ChartObjects.Add(0, 0, rng.Width, rng.Height)
pic.Chart.Paste
pic.Chart.Export "C:\Users\kris\Desktop\bazooka.bmp"
pic.Delete
Hi kris
I'd like to see the entire procedure if you will. Also, please use code tags around your code. Administrators are pretty strict on that.
John
Hi kris
Until I can come up with something more "bullet proof", try the attached. The procedure is still resizing the image to the original image's approximate size. However, if your range is "fixed" perhaps this will work for you.
Let me know of issues.
John
Hi John,
Thats working great but I have a small issue here.I am trying to display a particular date on the outlook template body so i am linking it to an excel cell that gives the required date.But when running the macro to send the email, outlook asks for a yes or no to update links.Is there any way to disable those pop up alerts from outlook and so that it automatically updates the links to that reference.
Thanks,
kris
Hi Kris
Where is the cell reference? Is it in your template? I'm not certain how to automatically update links in Outlook but I'll be glad to look into it.
If you've modified your template to include the cell reference, post a copy of the template. I recovered your old workbook from backup so I have that. If you've modified your workbook, a sample of the current workbook would be helpful.
John
Hi John,
Yes the cell reference is in outlook template.The template is pretty much the same.
I have a date in one cell in the excel file that we are using to generate the email.
The date goes in between the text in row in table 2 of the outlook template.
So is there any way to code date into that part of the text through excel VBA or else is there anyway to update the outlook links to that reference automatically so that it does not pop up with an(ugly) yes-no dialog.
Thanks,
Kris
Hi Kris
The date goes where? If you don't wish to post the template, at least post a picture of it (raw) so I can see what and where you have the reference coded.
John
Hi John,
Sorry for the late response.I am attaching a sample template.Date goes where text is red in color.I am wishing to put some (DATE - 9) value..i mean the date of the day that is 9 days before the current date.
Thanks,
Kris
Hi Kris
I'm sorry but I'm color blind. Beyond that, I don't see a cell reference in your template. Is this an actual copy of your template or one made for me. If made for me, just change proprietary data, leave all else the same. Please create an EXACT duplicate with only proprietary info changed.
John
Hi John,
It is exactly the same with just the data being changed.I have made that as 1 table with several rows.So the date goes in between where some text was present in the 2nd or 3rd row.It says <insert date>.
Thanks,
Kris
Hi Kris
I'm on the road this weekend...have fatherly duties...may not get back to this until Monday or Tuesday.
John
Hi Kris
I've been playing with this off and on. I'm unable to duplicate this issueThe template you uploaded appears to contain no links that I can find. I'm not certain why they didn't come across.outlook asks for a yes or no to update links
I don't know how to link an Excel worksheet cell reference to an Outlook Template. Perhaps you can educate me on that process and I may be able to figure out how to resolve the problem.
John
Hi John,
I appreciate your response.Yes we can link an excel cell reference by just copying the cell and then paste special with links in outlook.But the problem here is when I open the outlook it pops up a dialog box for updating links.I was looking for a way that automatically updates the links without prompting the user.
Thanks,
kris
Hi Kris
Yes, I finally figured this outbetween hanging the my Grandson's Ceiling Fan and the my Son and Daughter-in-Law's Kitchen Light Fixture. Haven't figured this out yetcan link an excel cell reference by just copying the cell and then paste special with links in outlookMy research hasn't provided ANY direction on this. I'll keep looking. I'll get back to you and let you know what I've figured out.I was looking for a way that automatically updates the links without prompting the user
John
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks