Take Contents Of Cells And Use Them To Compose An Email: Address, Subject, And Body
Feb 26, 2008
I need to take contents of cells (which are results of formulas and references to other cells) and use the contents to compose an email.
Here are some cells to illustrate. NOTE: the information in brackets is the formula that's contained in the cell, while the text is the result of the formula.
B3 = This is the link used to compose an email.
C19 = abc@domain.com (an email address)
C22 = Login expired on node citrix08. [=concatenate("Login expired on node ",C7,".")]
C25 = Dear Company A, [=concatenate("Dear ",C6,",")]
C27 = The login for citrix08 has expired. [=concatenate("The login for ",C7," has expired.")
C29 = Contact us within 15 days to continue. [=concatenate("Contact us within ",C9," days ...]
C31 = Ticket 444 has been created to track the issue. [=concatenate("Ticket ",C10," has...]
C33 = Lots of other text
D33 = More text
E33 = More text
C48 = Sincerely,
B3 (Named "SEND MAIL") contains the formula used to compose the email from the cells above, however, I cannot add all the cells to the formula without an error. At some point, there is too much data (contained in the referenced cells) and B3 returns #VALUE error.
=HYPERLINK("mailto:" & C19 &"?subject=" & C22 & "&body=" & C25 & "%0D%0A" & "%0D%0A" & C27 & "%0D%0A" & "%0D%0A" & "%0D%0A", "SEND MAIL")
The above formula works now, because it doesn't contain all of the body (of email) I need.
When I click on B3, it properly launches the email client, puts in an address, subject, and two lines of text in the body.
My workaround to this problem is to manually highlight cells C29 through E48, select copy, then go to the partially formed email and paste the rest of the body.
Is there a way to include all the body into an email? I think this can be accomplished with a VBA code, but have no idea how to do it.
I dont want to send the mail, just compose it using cells that have calculated what is needed in the body. I can then read the email, confirm it's content, and click send in the email client manually.
Does anyone have any suggestions? Is there a better (but simple) approach?
I know this could be done via javascript on an html page, but I dont know how to code this at all.
I do know excell quite well and have many cells to calculate what is needed in the body of the email, but, alas,
still can't compose the ENTIRE email from the formula in B3.
View 9 Replies
ADVERTISEMENT
May 10, 2013
I have an email hyperlink setup on my excel sheet and I would like to automatically (not manually) add a sentence that is another cell (that changes from time to time) to the subject line or the body of the email.
View 1 Replies
View Related
Dec 18, 2008
I use gmail, and have an excel sheet with hundreds of rows. I need to send each recepient an e-mail using their e-mail address (row D), and their name in the body and subject (row A). The rest of the body of the email will all be the same.
View 6 Replies
View Related
May 9, 2013
My task here is to generate email automatically. When i enter x to run the sub findvalue macro.
Any cells on the column D that has the value of 10 should generate email with the message body, subject and email address automatically.
Example if there are 3 task that are 10 days to deadline, 3 email will be generated after entering "x"
I have edited the Sendmail sub to locate the email's body, subject line and email from the excel.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target = "x" Then
If Not Intersect(Target, Target.Worksheet.Range("G2")) Is Nothing Then
Call Findvalue
End If
End If
End Sub
Code:
Sub Findvalue()
Dim Rng1 As Range
Dim foundemail As Range
Dim a As Variant
Set Rng1 = Range("D2:D10")
For Each a In Rng1
If a.Value = 10 Then
Set foundemail = Sheets("Email").Range("A:A").Find(What:=Cells(Target.Row, 1), _
[Code] .......
View 4 Replies
View Related
Mar 10, 2009
I have tried a number of things, but have not been able to figure it out. It may not be available with this particular code, but I am not sure what limitations this code has. Does anyone know how to include the contents of a particular cell in the subject line? Currently the subject line says "Notification." What I am looking for is for the subject to say "Notification - John Doe" where John Doe would be the contents of cell D13.
View 2 Replies
View Related
Dec 1, 2011
Is there anyway this can be done using a button in the spreadsheet?
For Example.
I send numerous almost identical forms to a colleague and to eliminate subject line typo's (Reference numbers) i would like the Subject line to match a cell in the document itself.
I have experience in working with Excel, but none on using code/macros
View 2 Replies
View Related
Jun 17, 2014
I am trying to write a macro to compose an email in outlook without sending it. Ideally the macro would compose the email and the email would be left open for me to inspect before sending. Below are the details.
Column A contains the addresses
Column B contains the CC addresses
Cell C2 contains the message
I can change where the above info is located/arranged if need be.
My spreadsheet automatically generates the email addresses, which are dynamic. For control purposes my employer does not want me to have the email automatically sent, being the reason for me to want to inspect the email before sending it.
View 2 Replies
View Related
Aug 10, 2009
I am trying to send out an e-mail from excel with all kinds of combined info. Here is what I have so far, which works to send out an e-mail to a certain address with a compiled subject. No luck with inserting the BODY of the e-mail yet from a certain cell.
=HYPERLINK(CONCATENATE("mailto:",E32,"?subject=",F32))
Also, How do I make the text in each of these cells read "send e-mail" rather than the whole jumbled mess that is currently in there from the combination of the cells?
View 6 Replies
View Related
Nov 17, 2009
I receive around 40 emails per day, all in the following format:
Subject: "Team x.x - date"
Body text: "x files sent"
where x represents a number.
What I'm hoping to be able to do is drop all these emails into a folder (in .msg format), and use excel to look through them and extract the team number from the subject, and the number of files sent from the body text for each message.
Unfortunately, while I class myself as reasonably competent with excel vba, working with emails or .msg files is new to me, so I genuinely haven't a clue how to do this, or if it's even possible.
View 7 Replies
View Related
Aug 3, 2006
I have a form which is going to be used as an aid to staff, I have created Macro's that when clicked enter information in to fields in the worksheet, I would also like the macro to generate an email with a specified Subject and body filled out. At the moment all I have achieved is to generate an email through File - Send to - Mail recipient.
I have also tried to have an email created through Outlook using MAPI, but once again have been unable to create an email with or without a subject and message body. Below is how far I have got in creating an email using MAPI.
This is part of Macro to insert information Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("E12").Select
ActiveSheet.Paste
Call SendAMessage
End Sub......................
View 2 Replies
View Related
Nov 7, 2013
I would like to search an outlook folder for a variable in the subject line and return the date when that email was received. Here is the code i have so far...
Code:
Sub Get_pos()
Set olApp = CreateObject("Outlook.Application")
Dim olNs As Outlook.Namespace
Dim Fldr As Outlook.MAPIFolder
Dim olMail As Variant
Set olApp = New Outlook.Application
[Code] ........
View 2 Replies
View Related
Apr 21, 2006
I am using the following code to automatically send an attachment to a specific email with a specific subject line.
ActiveWorkbook.SendMail Recipients:="johndoe@yahoo.com", Subject:="General subject"
ActiveWorkbook.Close SaveChanges:=False
Is there a way to reference a specific field in the active workbook as the subject line?
View 4 Replies
View Related
Nov 28, 2008
I would like to select File/Send to/Main Recipient (As Attachment) within my Excel Spreadsheet. Within the email, I would like the subject line to be automatically filled in with an entered value of a cell block from within the spreadsheet.
For Example, If cell block A1 had Thursday entered in the cell. Then once I chose send to/Mail Recipient, The subject line would read Thursday.
View 4 Replies
View Related
Jul 22, 2014
I currently have the below in my VBA which adds the text to the subject of an email:
Application.Text(today - 1, "MM-DD-YY")
today = Int(Now()) is part of the macro. How do i format this to display workdays only.
For instance if today is 07/21 (monday) i need this to display 07/18 vs 07/20.
In excel that function would be =WORKDAY(A1,-1) where A1 is =Today(), just not sure how to translate this to VBA.
View 5 Replies
View Related
Aug 18, 2006
I need to combine a first name with last name, combine them with a domain to
create a useable email address.
View 9 Replies
View Related
Oct 16, 2008
I have a worksheet with three columns. First Name, Last Name and email address. I need to combine the first name with the last name and add our @ourcompany.com to the end of it for the emaill address.
I've figured out how to do that, but my problem is, we don't use our first and last names in the email address. We use first initial and last name.
I know how to make Tom Jones become tomjones@ourcompany.com
What I need to know is how to make Tom Jones become tjones@ourcompany.com.
Can someone point me in the right direction here? I just need the first initial of the first name and combine that with the last name and then add in the @ourcompany.com.
View 3 Replies
View Related
Mar 19, 2012
I have the below code of which I would like to addresses added to the CC list Also, Would it possible to add a range as the body? E.G:
.CCaddress = "somebody@mail.com"
.CCaddress ="somebody.else@mail.com"
Body text = Range("A1"& "" &"B1")
Code that need the above inputted:
With ActiveWorkbook
.SendMail Recipients:=Array("steve.howard@kuehne-nagel.com"), _
Subject:="610 Cambridge " & Format(Date, "yyyymmdd") & " " & "Counts"
[add cc address + body]
.Close SaveChanges:=False
End With
View 5 Replies
View Related
May 3, 2009
I currently have a workbook that I track various projects with and periodically I will need to get updates for them. What I want to do is to select the row of the projects I need updates for, copy them and then paste them into the body of an Outlook 2003 email.
View 10 Replies
View Related
Jul 14, 2008
I have a file that sends an email when it's saved using the macro.
It pastes the the file name and file location into the body of the email.
I'd like it to be a hyperlink.
Sub Save()
Sheets("Datasheet").Select
Range("AW6").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 1
Dim strDate As String
Dim strSave As String
strDate = Worksheets("Datasheet").Range("ay6").Value..........................
View 9 Replies
View Related
Mar 10, 2014
Cell A is the first name, B is a period, C is the last name, D is an "@" and E is "yahoo.com".
I want to squeeze them all together, in order, without spaces, into cell F to make an email address.
What is the formula ?
View 5 Replies
View Related
Jun 20, 2014
Please see attached workbook
At the moment when the workbook is opened the first time in the day emails are auto sent for any overdue items (all good)
1) there is a column with lead times in each sheet that should send a warning email when the lead time number of days
is reached( this sends the email but does not populate the table)?
Question how can i amend the code to either make the lead time populate the summary sheet or remove the lead time option
2) Also i have copied the template sheet over to add more shhets but the email table dont populate.
How should i be copying new sheets over?
1018823(1).xlsm
View 4 Replies
View Related
Aug 13, 2014
I'm trying to add a range to this email code that will then paste the information into the body of this email however instead of getting the data I need i'm getting "-1" in the body instead.
I've tried defining the range below and adding that line to the main code but it doesn't work, the code will run but i get "-1" in the body.
View 13 Replies
View Related
Mar 21, 2014
I have a process metric that I need to send via email.
I would like to automate this.
I am looking for the following
1. upon clicking send mail button the smart art used on the excel should be mailed.
2. the smart art should be pasted as Image on the body of the email.
3. The body should contain a text message like " dear receiver," <line break> " Please find the below process health for this week" <line break> "Regards," <line break> "Sender"
4. The size of the image on the email body should be height 3 inches and width should be 5 inches.
I have attached the spreadsheet for reference.
View 10 Replies
View Related
Feb 2, 2012
insert image in the body of the email.
Sub Mailer()
'Sheets("BB Email Data").Select
pathname = Range("A1").Value 'defines attachment
Dim objol As New Outlook.Application
Dim objmail As MailItem
Set objol = New Outlook.Application
Set objmail = objol.CreateItem(olMailItem)
[code].....
View 1 Replies
View Related
Aug 14, 2012
I am using the below code to send a email by VBA in Excel but I am trying to add a hyperlink in the body of the email.
I would like to use '"Click Here" to go to Google - I only want to show "Click Here" in the email not the full web address
Sub SendEmailData()
Dim iMsg As Object
Dim iConf As Object
Dim cell As Range
'Dim strbody As String
' Dim Flds As Variant
[Code] ........
View 9 Replies
View Related
Nov 29, 2012
I did this 10 years ago so I admit I am rusty at this but here are what I had in my notes that worked with Excel XP. I am currently using Excel 2010
With ThisWorkbook
.HasRoutingSlip = True
With .RoutingSlip
[Code]....
The last line will send the workbook but I need a Subject and Message to go with it.
View 9 Replies
View Related
May 17, 2013
I have a form in excel (very simple) that I need to put on the web - People can then fill out certiain bits of the information and the idea is that they then click on the button to email it back to me. Ie complete it there and then and email it straight away so that they don't need to save it etc.
I can't for the life of me work out how to do this! I've managed to get the macro to open an email with the correct details on but not paste in the information so am at a complete loss and appear to be going round in circles!
View 2 Replies
View Related
May 22, 2014
I am struggling to find a macro which can look at a name in column 'BT' and search it in the address book of Outlook to then place the email address of that person in column 'ED'
There are 35,000+ people in the address book and there may be over 5 email addresses for one name, so is there any way a message can appear for the user to select which email address is correct if there is more than 1 contact for that name?
View 1 Replies
View Related
Jan 18, 2013
I have an email with body in tabular form. I need to copy this into excel in the as is condition.
When I do it manually, I see that the table nicely fits in different cells and looks good, but when I use a macro , I see that everything gets copied to a single cell.
View 6 Replies
View Related
Jan 30, 2014
I've wrote a function that allows me to insert a certain range to the body of an email. This email is composed in the code, too.So far I am able to build the emails body as plain text. how to retain the cell format also (colors, font size, border styles, ...)
View 14 Replies
View Related
