adding a signature to email though excel

[daveeps]

hi ,
i had some help on here a while back creating some code to save files as a .pdf to a certain folder and then to add one of the sheets to a email ,
this has worked great , but i now have other people using the files ,
and want to make parts of the sheet easier / foolproof to use !

basically i would like to add the default outlook signature , so no matter what computer the files are on it will always add whatever the default signature is,
and i would like the to email address to be whatever is on cell D10 of sheet QUOTATION
and also add on the email body "To / Hi" to whatever is on cell D11 of sheet QUOTATION

i will add the code below
if any of this is not possible , then not a problem
but any help will be much appreciated

Please Login or Register  to view this content.

thanks in advance
dave

[aworfolk]

This might Work for You.

Please Login or Register  to view this content.

[daveeps]

hi aworfolk

many thanks for your reply ,
i tried this but this line kept coming back as an error 9

Please Login or Register  to view this content.

[aworfolk]

daveeps

Try this. I have taken the code I use and tried to input your information in.

Please Login or Register  to view this content.

[daveeps]

unfortunately this isn't working for me either ,
comes back with an error

compile error :
User - defined type not defined

i apologize i have very basic skills regarding VBA .
so all im doing it copying your code and testing

[aworfolk]

That is fine could you please hit the Debug and paste what line is giving this error so I can look into fixing it.

Thanks

[daveeps]

this is what the dialog box says

compile error :
User - defined type not defined

and highlights this bit of the code

Please Login or Register  to view this content.

[aworfolk]

I found where I mis-defined the body.
Sorry about that

Please Login or Register  to view this content.

[daveeps]

still the same compile error and highlighting the same bit of code which is the first line

Please Login or Register  to view this content.

[aworfolk]

Lets check something.

In the VBA Screen go to Tools.
Then References.
There should be a box checked call Microsoft Outlook 16.0 Object Library.

If not scroll down and select this box.

[daveeps]

yes this is selected ,
i remember doing this one last time as it was unchecked and parts of the code didnt work

[aworfolk]

I am running out of Ideas for this Problem.

Here are the References that I have checked.

Visual Basic For Applications
Microsoft Excel 16.0 Object Library
OLE Automation
Microsoft office 16.0 Object Library
Microsoft Outlook 16.0 Object Library

[daveeps]

no problems ,
thank you for trying ,

can anyone else help ?

[aworfolk]

Try to download this file and see if you can run it through this.
If this does not work then I can not help sorry.

[daveeps]

i have downloaded the file ,
opened it and hit the generate email button.
the new message box opened up but with no default signature on ,
this is what was in the email body ,

To/Hi,
tester
Add Signature Here


looking around the internet for a fix on this and seems many people have problems with adding the signature,
and with very conflicting coding.

but many thanks for all your help

[aworfolk]

In the Code you can write out your signature line where it says "Add Signature Here". Then remove the ' from the front of source_file and .Attachment. If you need the email to go the next line for the signature Add & vbNewLine & then add the next line.

so essentially something like this.

Please Login or Register  to view this content.

[KOKOSEK]

Do you want to add default signature which you got set in your outlook?

[daveeps]

Do you want to add default signature which you got set in your outlook?

hi ,
yes but using the code in my first post .

is this possible ?

[KOKOSEK]

First you have to locate file/files on your computer where signature files are located.
It should be something like this:

44279_1.png

you also need this function:

Please Login or Register  to view this content.

then in vba:

Please Login or Register  to view this content.

and in "Signature" you've got now your signature, so:

Please Login or Register  to view this content.

In complete looks like:

Please Login or Register  to view this content.

[daveeps]

i have copied and tried the code adding my signature location ,
but seem to get an error.
here is my findings

runtime error `52`:
bad file name or number

highlighting this code

Please Login or Register  to view this content.

i have tried this in a new file too just in case there is no conflicts with what i have on my worksheet

[KOKOSEK]

Error is clear

Please Login or Register  to view this content.

are You correctly set file name and path in this line

Please Login or Register  to view this content.

add

Please Login or Register  to view this content.

and check that file is there and its name is correct

[daveeps]

so ive had a play with the code and this is what ive found .
using F8 i have gone through the code by each string.
once the email displays, the signature is there until it adds the next code

Please Login or Register  to view this content.

then the signature disappears and is replaced by my email body text.

i have tried

Please Login or Register  to view this content.

but seems to do nothing .
and ive tried the above code which gets the signature from the file and that also seems to do nothing ?
any help will be much appreciated
here is where i have got my code too now which works perfect but with out a signature

Please Login or Register  to view this content.

[KOKOSEK]

Try this:

Please Login or Register  to view this content.

and check attached file (put both files in the same folder and 100% works)

p.s. I have to zip mysignature.html because forum does not accept html files as attachment.

[daveeps]

ive tried this and still doesn't seem to work .
the email is all there as it should but no signature .

i tried it on your sheet which i downloaded and also on my excel sheet.

if i go back to my sheet and delete this code

Please Login or Register  to view this content.

then the email comes up with the signature ?

[aworfolk]

Not an expert with Excel to Outlook but from the testing I have done the email generates with signature filled in and then when you edit the body as what the excerpt of code you show does it erases everything and replaces it with the text you provide. I would suggest adding your signature to the end of the code to look something like this:

Please Login or Register  to view this content.

[daveeps]

thanks for your testing on this ,
i can see what you have done and i did have this type of text signature before,
but on my standard email i have an image signature through outlook and wanted this to match up .
also my colleagues have their own signatures which is different to mine that is why i was trying to have the default outlook signature set .

but after lots of googling and adding lots of different codes im thinking its not going to work to have a image signature and a auto text body at the same time .
seems like its either one or the other !!

[aworfolk]

Sorry,

This is out of my wheelhouse. If you or somebody in the community can get the body of the Email before you enter in your information as a variable, then the variable will have to get added to the end of the body.

[daveeps]

No need to apologize 😁
Thank you for taking the time to look into the problem for me,
As I said I'm quite new to VBA, I can do basic code and I can see what I want to do but not the knowledge to implement it !
Hopefully someone who has will have the answer
But thanks again for trying ☺️