hi all,
I was hoping you could help me with the code for the following:
(Sheet 1)
I select B23 / 24 / 25.... etc
macroassignedbutton_click to do the following:
Find corresponding email address in O23 / 24 / 25....
Open embedded object (on Sheet 4) thanks.msg
Paste email address into To: field of outlook msg
Send automatically(?)
Insert "msg sent" into Q23 / 24 / 25....
display message box to confirm action is complete
eagerly awaiting your help!
many thanks
Hi,
You didn't mention what you want to do with the thanks message. I've assumed this is meant to go in the body of the email so I've used an Input box rather than using a sheet 4 embedded object.
This is also a sheet event 'before double click' event macro so just double click the relevant row. If you want it instead attached to a button just create anormal module level procedure.
Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Hi Richard,
Yes, the thanks.msg is the body of the email... (also... can you pre-write which mailbox it gets sent from?)
It would go as a button-click module... will that do anything to the above?
That's why I wanted the code to simply open a presaved message... as the mailbox will be saved and the body email and subject line already completed in the 'template'.
the only action i want the code to do is open it... send it the the relevant email address in the row when prompted with a button-click called "send email"
if the code can then continue on to update the row to say it's sent, and to display a message to the user confirming that - then even better.
hope this clarifies.
Last edited by AlixNB; 04-22-2014 at 06:03 AM.
Hi,
Not sure what you mean by 'assign a code to it'. Tell me which line is painted in yellow when you get the Debug. I'm assuming of course that you're using Outlook as your client email software. The original code assumes that the "FROM" is the user who's creating the email. In the version below you can change the sent on behalf of email address..
In a standard module level procedure which you can attach to a button
Please Login or Register to view this content.
hi,
yes = outlook.... (the above looks like you've resolved the outbox for me - thank you)
the inputbox for the body is what is (while working) not quite right... as the email template I already have is coloured with embedded images and hyperlinks etc... it's not practical to re-write it everytime.
Is there a way just to send the email object, from dictated box, to whoever is selected, a the click of a macro enabled autoshape (button)?
please bear with me x
Hi,
Sorry, but I don't understand what you mean by the 'email object, from dictated box, to whoever is selected'
Perhaps you'd better upload the workbook you're using and an example of the template you're using.
steMail = Range("O" & ActiveCell.Row)
.SentOnBehalfOfName = """SenderName"" <senders email address>"
.to = steMail
the code above is working great, where it opens an email, sends it to the address stored in column "O", and sends it from the mailbox that I need it to (once I update the name and address as required).
but instead of opening the email template already kept on sheet 4 (which has been embedded as an object) the code you've helped me with opens up a new - blank email, where your inputbox is asking for the body of the message... rather than loading the embedded email i've already designed.
is there a way to open the email i have embedded on sheet 4 and send it to steMail = Range("O" & ActiveCell.Row)
and sent from .SentOnBehalfOfName = """SenderName"" <senders email address>"
so it's the body of the message that i cant get right, because there is already a template available... i'm just not sure how to get it to open with the code you've helped me with?
Hi,
I'm spectacularly failing to understand what you mean by your email template on sheet4. What is it exactly? An embedded picture/image?
As suggested it would be useful if you could upload your workbook.
I can't upload due to server restrictions....
but, in excel:
>Insert / Object
> Create from file / Browse...
> Tick Display as icon / Okay
that is how i've embedded the pre-saved outlook email (complete with From:address and all the table layout, images and text)
So Insead of having a code that opens a new message and asks me to type the body of the email... i want it to open the 'object' ive embedded into sheet 4...
sorry, i know i'm not explaining it very well.
...but what is the 'object' in sheet 4?
A picture inported from a .jpg file? ...something else???
An Outlook .msg file .... so an email template where the to:field is blank (awaiting population from the code/column O)
according to excel its an 'OLE Object' [whatever that is]?
Sorry to be a pain
would it be easier/better/cleaner if i change how the .msg is stored?
Hi,
I'm finding it difficult to visualise exactly what you are describing.
This is one of those rare occasions when we probably do need to see a picture of what you expect the email to look like. Please upload both the picture and also the workbook you're using.
email img.png
this is the only way i could do it due to server restrictions
hope it helps?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks