screenshot text file and embed in outlook mail through excel with mail info

Hi All,

Here is the challenging task i encountered in my work.

I want the excel to create a outlook mail with To , Cc , Subject and Body of mail
which all are unique. the thi8ng is it should generate each mail with a screen shot of text files content.


The text contains the customers information and their invoices, i get report in text .txt format
with all customer invoice information of various regions in order of one by one separated by many empty lines.
I need every single batch of invoice information in email in image format (screen shot).

Why image format?

because the recipients should not make any changes by editing and manipulate.


Can this be achieved...i have attached a sample text document....now i have limited resource hence i cant attach excel file.


Please come back, if you need more or clear information.

im sorry the text file isnt much help

would this not be what you are after?
http://vba-useful.blogspot.fr/2014/0...ed-images.html

hum,

Thanks... will give a try and come back....

I have to come with some other way to have the screen shot in mail....

If any ideas, i appreciate

Hi hum,

I have attached a excel, where i pasted the text file data, and i copied it and paste special as image in outlook email and it does. Why cant we design a macro to copy each set of data and create a mail and paste it in each mail....

Will this way works... am trying using the link you provided... but nopt getting right codes to fetch each region data ( frankly speak i don't know the array, loop thing perfectly)

AM also cracking my head using various codes available in internet... Can you also give it try and help me please....


Thanks a lot for your precious time.....

so i took the code from the link i provided and changed it to your needs

i modified the code to suit what i think you want
i did modify some of the data as i didn't see the point of having the image and the wording in the email itself saying the same thing (you will see what i mean by that)

i didn't modify the email to, cc as there was nothing in the data to indicate otherwise

the loop for the image seems is a bit convoluted but it does the job (i hope) with the data structure you have in this sheet
if data structure is different it will/may not work

actually think you dont want image modifier

see attached without image modifier
i think its cleaner
h variable is not needed at all in this case

sendmailv2 added to show the difference

This is pretty impressive stuff.

The new streamlined macro sendMailv2 is very sharp.

The screen capture gives me ideas for all kinds of mailmerge-esque functions that I'll be reverse engineering for weeks.

i was quite pleased with v2 outputs as well
the logic of defining the range is a bit hap hazard but it does the job (for the data given)

all credit should go to original link though
that's where it all came from of which i just re-engineered to answer OP specific question

Hi hum,

Am very much pleased to see the macro working 200% as i expected.... million+ thanks.

i took some day to reply you because i know that i will make errors, so that just i took some time and validated the output with my sample and original.
As usual i got provided an worst sample that doesn't match with my original.

Can you please brief me the below piece of code doing ( also may how loop works here)... so that i will work out by modifying to match my original...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

My original file needs to be converted from text to column and rum this makes worse to modify the code.

I have one request if am not give text to column and just autofit the column A with data and try to make the screenshot of data from a single column what will be code? which part of code to be replaced?

Thanks for your time man..... you made me proud where am presenting this macros... ofcourse i have told everyone that i have got it from forum...

you may need to the look over the overall loop if your data is different
im sure someone else could come up with some other fancy way to achieve the area for image

Please Login or Register  to view this content.

So this is for the first run thru the loop
I would naturally be 1

i think you understand the majority of it, this may
Range("d1").Offset((Range("D1").End(xlDown).Row), 1).End(xlUp).Row
be the bit that requires explanation

based on your data setup
breaking this down

so start at cell D1
Range("D1").End(xlDown).Row = ctrl + down arrow from d1 = 35
so offset(35,1) = E35

then .End(xlUp).Row
ctrl + UP from e35 = E11 = only want row number so 11

so imgRNG = "A" & i & ":E" & Range("d1").Offset((Range("D1").End(xlDown).Row), 1).End(xlUp).Row = A1:E11

then j = Range("D1").End(xlDown).Row = 35

which sets up for the next loop

Please Login or Register  to view this content.

Range("d" & j).End(xlDown).Offset(0, 1).End(xlUp).Row
same principle as above code
ctrl down from D35 offset 1 column to right, then ctrl UP = 51

imgRNG = "A" & j & ":E" = A35:J51
next J is ctrl down from D35 which is row 74
etc

I have one request if am not give text to column and just autofit the column A with data and try to make the screenshot of data from a single column what will be code? which part of code to be replaced?

not quite sure what you want to do here

you may have to give original format for me to understand

Thank you hum.... for your explanation, it was clear

regarding my doubt...

I have one request if am not give text to column and just autofit the column A with data and try to make the screenshot of data from a single column what will be code? which part of code to be replaced?

I have attached the sample workbook, that is all the data would be in column A itself... in that condition how would be the loop codes?

Thanks for your time friend...Thanks.

HI hum,

I found the error part as per below your explanation, D:D has the region and it act as the start point and give the coordinates as A1:E11
to take the screen shot, but ion my original file, region was in F:F column and when it do below

Please Login or Register  to view this content.

VBA cannot able to identify the cell (F:F) in A1:F--- as coordinate...

so start at cell D1
Range("D1").End(xlDown).Row = ctrl + down arrow from d1 = 35
so offset(35,1) = E35

then .End(xlUp).Row
ctrl + UP from e35 = E11 = only want row number so 11

so imgRNG = "A" & i & ":E" & Range("d1").Offset((Range("D1").End(xlDown).Row), 1).End(xlUp).Row = A1:E11

then j = Range("D1").End(xlDown).Row = 35

which sets up for the next loop

I have attached the sample excel.... hope i made myself clear...

Its my misyake, as i would made the sample as correct in first point... sorry please help me.... Thanks...

ok i am thoroughly confused now

post #11 is the original file?
post #12 is how you are outputting thru your text to column?

how are you splitting it with text to coloumn? i cannot replicate and where does the money part come from?

Hi hum,

lets forget/forgive all confusions i made.

I attached the text original text file and excel file with text to column converted and text to not converted.

In text to column file i also recorded macro, how im doing text to column. Only thing i would add a break line at lat to seperate the refion number as one column.

In no text to column file, i didn't made text to column, i just pated the data in excel and autofit it in column A itself....

My need is

where my before sample shows the region number part ( that you used a key part to fix the range to be screenshot) is "D" and "E" column has the date part.

but i made made wrong sample by that. Now the file i provided is the final and original which has the region number key part in column "G" and it is the column with data where the offset can't be worked based on your codes.

Hence i tried changing codes but i failed...

So i need to customize the codes to suit this final file.... please help....

and the second file without text to column has only one column "A" data and i tired in this scenario.... but its no neccesary just i tried...
If you can you provide for that also, i will also learn the logics can used...



Thanks a ton for your time friend..... Hope i won't make you tired in this post further....

Please come back if any more need you have.


THANKS....

Hi hum,

lets forget/forgive all confusions i made.

I attached the text original text file and excel file with text to column converted and text to not converted.

In text to column file i also recorded macro, how im doing text to column. Only thing i would add a break line at lat to separate the region number as one column.

In no text to column file, i didn't made text to column, i just pasted the data in excel and autofit it in column A itself....

My need is

where my before sample shows the region number part ( that you used a key part to fix the range to be screenshot) is "D" and "E" column has the date part.

but i made made wrong sample by that. Now the file i provided is the final and original which has the region number key part in column "G" and it is the column with data where the offset can't be worked based on your codes.

Hence i tried changing codes but i failed...

So i need to customize the codes to suit this final file.... please help....

and the second file without text to column has only one column "A" data and i tired in this scenario.... but its no neccesary just i tried...
If you can you provide for that also, i will also learn the logics can used...



Thanks a ton for your time friend..... Hope i won't make you tired in this post further....

Please come back if any more need you have.


THANKS....

amendments to IF loop done
i have tweaked your text to column macro

Please Login or Register  to view this content.

it copies Region 1 to G7 so every region is a square box
also renames sheet to email
i greened out your Saveas section so put it back when you rework it

the main logic of the imgrng is the same but instead of offset to the right it is offset to the left

Please Login or Register  to view this content.

Dear Hum,

I'm getting debug prompt saying, "Object out of range"

and highlighting below code...

Please Login or Register  to view this content.

I have attached a attched a sheet with a correction. That is my company name comes in all region data...

I think it is causing that error.... i understood the offsets well....

Can you please take a look of this attachement and amend change in codes...

Thanks..... damn sure soon i will mark this thread solved.... Waiting for your turn.

Thanks a lot...please come if any concerns.

you did not run my code for text to column?
attached sheet in #17 does not look anything like mine
or did you not give me the text file you normally get?

anyway
i ran the code thru file attached in #17 and it works fine
it may be the conflict in your saveas section in text to column macro
are you using that?

NO am not using text to column, just now i tried the # 17 attachment and it showing debug error as "ActiveXcomponent can't create object"
and highlighting below code

Please Login or Register  to view this content.

am confused, will take some time and come back... but my data are cooll and there are no changes...

If you can follow my data and the macro i need, please carry on with your help... or please wait i will come back after watching the sheets some more time.

Thanks for your time.

Does it have any issue when you run macro on attachment #16?

Dear Hum,

actually had to made some more corrections in file, # 16...

also tried some minor changes like G7 to G1... etc... and works well....

you can see the attachment, to know the changes how it looks...

I will try it out in my work place and confirm you... in home its working, but i went there and tried it gives me bug... am doing very minor error but cant figure out...

Thanks will come back.... and change the smiley from to

Dear Hum,

At last it Works well...Thank you so much...

Solving the post.... you deserve all credits.

good job


glad to help

Dear Hum,

Belated Happy New year to you and your family....
Hope you are doing very good...

Sorry to extend this solved post....

I have question....

Is it possible to control number of mails to generated by an input box like that....

Like, if i give from page # and to Page # , macro should generate only that specified page nos mail....

I encountered an huge spike in volume of my accounts and need to generate large number of mails.... hence i would to do it like this way...

(Am afraid that if i generate 2000 emails at the same time, what my system will do..)

Expecting your reply...

Thanks a lot Hum...

and to you too good sir

i would imagine you could set another criteria for execution

essentially have input box at start of macro...then somewhere down before outlook portion of code have a check to see if you want to run the outlook portion but you are going to be much more specific than your above post

alternatively
have another macro code to separate exactly the data you want only to mail out then run sendmail macro on that data only