Adding CC Address And Body To SendMail Function?

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


ADVERTISEMENT

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 View Related

Adding Range To Email Body?

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

Email Hyperlink Adding Body / Subject Text?

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

ActiveWorkbook.SendMail

Apr 8, 2009

I have the below code,

But i want to adapt the "ActiveWorkbook.Sendmail" so I can attach my current workbook as it is, to the email.

Private Sub CommandButton10_Click()

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "you@email.com"
.CC = "me@email.com"
.BCC = ""
.Subject = "Testing send to multiple emails."
.Body = "Please review." & vbCrLf & vbCrLf & ActiveWorkbook.FullName
.Send 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing

Dim Msg, Style, Title
Msg = "E-mail has been sent " & Chr(13) & Chr(10) & "Press OK to continue."
Style = vbOKOnly + vbInformation
Title = "Open Issues List"
Response = MsgBox(Msg, Style, Title, Help, Ctxt)

End Sub

View 9 Replies View Related

Sendmail Without Attachments

Mar 23, 2007

Is there a way to use the function Sendmail without sending the Workbook, sheets or any attachments at all. I just would like to push a button in my Workbook so that a mail goes to the recipient saying for example: "Check our shared folder.

View 4 Replies View Related

SendMail Macro With Cell Ref

Sep 15, 2007

I am using a sendmail macro I found online. I cannot remember where I found it or who wrote it.

I need to change it so that it references a cell (HTOM!C2) for the email instead of a static address. The portion I need to change is 14 lines up from the bottom.

Option Explicit ....

View 7 Replies View Related

Address And Match Function Together

Feb 23, 2014

Using the address function to find the exact cell of a unique string text.

I'm using the following command to find the address of a string i have in a different cell.

=ADDRESS(MATCH("*$H$1*",A:A,0),1)

The string text is in cell H1, H2, H3.......(or on another sheet). The goal is to find the address of a cell containing the unique string text from cell H1, H2, H3.....(that entire column) in a sheet with 13,000 lines. Basically, searching for a specific cell address of a string text referenced from another cell.

View 2 Replies View Related

Using ADDRESS Function In AVERAGE

Aug 11, 2009

I need to use the ADDRESS function to define a range from which an average can be determined. My current function is as follows:

View 3 Replies View Related

ADDRESS Use Inside A Function..

Feb 8, 2010

I'm trying to calc a Simple Moving Average of financial Fund data (row=fund type, column=date) on a very large matrix for a particular fund and date range. I locate the Fund of interest and retrieve the Start and End dates of interest and retrieve their addresses using the ADDRESS function. Since ADDRESS returns a text string, HOW DO I incorporate it's results into the following array function (which ignors blanks and 0 cells): {=AVERAGE(IF($L$10:$V$10>0,$L$10:$V$10,FALSE))}?

View 5 Replies View Related

Use ADDRESS Function In HLOOKUP?

Aug 12, 2012

I am getting #VALUE! when I use the formula to dynamically load some values to the column

simplified formula will look like below.

=IF(HLOOKUP("C",ADDRESS(ROW()-1,COLUMN(),4 ),1,FALSE)="C","C","A")

check the row above this/current column exact match if the value in that row is "C" then put "C" in this/current row and column else put "A".

example:
C
C
C
? (should be C)

A
A
A
? (should be A)

B
B
B
? (should be A)

View 6 Replies View Related

Function To Find The Address Of A Cell

Dec 8, 2005

I want a function that allows me to enter a range and then returns
the address of the cell with the minimum value. It is the Address I am
interested in, not the value.

View 14 Replies View Related

Function To Convert Long Value Into IP Address

Jul 29, 2014

Any function to convert a long value into an IP address. I have an extract from a mySQL dbase that has the ip addresses in long format. I need to reverse them back to IP Addresses.

View 1 Replies View Related

Result Of Address Function In Formula

Nov 13, 2006

How do you use the result of the "Address" function as ranges for other formulas?
I have 2 cells A1 and A2, each containing an "Address" function to find the start cell and end cell of a range.

A1 shows the result "$J$6" and A2 "$AB$6". These are working fine:

A1: =ADDRESS(ROW($A6),MATCH(HLOOKUP('cleaned up'!$I$2,pivot03!$2:$2,1,FALSE),$2:$2,0),1)

A2: =ADDRESS(ROW($A6),MATCH(HLOOKUP('cleaned up'!$I$5,pivot03!$2:$2,1,FALSE),$2:$2,0),1)


I want to get the average of this range using cells A1 and A2, but Excel treats the formula as an error: =Average(A1:A2) shows the error #DIV/0!

Going back one step and putting the formulas within cells A1 and A2 into the average formula doesn't work at all with a colon:.....................

View 3 Replies View Related

Define Range With Address Function

Dec 26, 2006

I am trying to create a correlation matrix that uses the =ADDRESS function to define the various columns of data which I want to correlate against each other (I require this flexibility because the number of columns and rows of data to be correlated will change). The basic formula I am using is something like the following: =CORREL((ADDRESS(Sheet1!$C$2+3,L2+2,,,"Sheet1")),(ADDRESS(Sheet1!$B$2,L2+2,,,"Sheet1")))

However, while the individual ADDRESS fomulas deliver the correct start and end cell references required, it seems that CORREL function won't accept the cell addresses that result from using the =ADDRESS function.

View 5 Replies View Related

Referencing Cells In Other Sheets Using Address Function

Feb 7, 2012

I'm building a table that references a dynamic range on a pivot table. So far I've gotten the function to return the address on the pivot table but it wont return the value. I'm doing this so that I can both refresh the two source data sets and summarize (pivot table) and also graph these two summarized data sets since you can't graph two different pivot tables together.

This is my code so far:
Iferror - To return zero if the source data doesn't exist yet
Address - To concatenate the match functions
Match - to find the row and column numbers on sheet "B" that match the x,y call on the original sheet "A"

=IFERROR((ADDRESS((MATCH($A2,'Install Data Pivot'!$A:$A,0)),MATCH(B$1,'Install Data Pivot'!$4:$4,0),1,1)),0)

Returns $B$5 since this is the cell where the information lies.

I've tried just adding the sheet name like this...

=IFERROR('Install Data Pivot'!(ADDRESS((MATCH($A2,'Install Data Pivot'!$A:$A,0)),MATCH(B$1,'Install Data Pivot'!$4:$4,0),1,1)),0)

But that doesn't work. I've also tried using different variations of Indirect and Index but I still can't get it to reference the other sheet.

View 1 Replies View Related

Vlookup Function To Return The Cell Address

May 18, 2002

Is there a way to get the vlookup function to return the cell address, rather than the value, of the lookup?

View 9 Replies View Related

Use Range Address From Cell In Index Function

Sep 15, 2007

I have a sheet with multiple tabs - 1 per employee. I'm trying to run an index on a select tab based on the contents of a cell in order to do a summary across all employees/tabs.

The person's name is Tulley and is listed on the summary page in cell A11. =INDEX(TULLEY!$B$4:$M$5,1,$B11) returns the correct value. I would like to replace the tab reference with the name of the person concatenated with an exclamation point. This yields TULLEY! -

INDIRECT("A11")&"!". I thought that I would be able to replace the TULLEY! in the index formula with the indirect formula. This formula returns TULLEY!, but the index function doesn't work: =INDEX(INDIRECT("A11")&"!"&$B$4:$M$5,1,$B11).

View 6 Replies View Related

Sum Function With Address Function

Apr 22, 2008

On my worksheet =ADDRESS(18+BH8-1,60,4) gives me BH21. However = SUM(BH18&":"&ADDRESS(18+BH8-1,60,4)) gives me the #VALUE! error

View 2 Replies View Related

Lookup Data From Multiple Worksheet Using Indirect Address And Match Function

Sep 28, 2011

determining the correct formula to lookup various details from multiple worksheet:

Data as follows:
Sheet1: input
AB1MonthSales2AUG55003MAY4000

Sheet2: Data

AB1MonthSales2JAN20003FEB25004MAR30005APR35006MAY40007JUN45008JUL50009AUG550010SEP600011OCT650012NOV700013DEC7500

b2,b3 Formula = Indirect(Address(match(a2,????,0),1,1,1,"Data"))

i dont know what is the correct range inside the match fuction to identiy the row number inside the Data sheet, i think the solution would be match(a2,Data!a2:a13,0) but things might get complicated if you have multiple worksheet such as sheet2: Data , sheet3: Data2, sheet3: Data 3

and each table has unsorted details compared to others sheets (Data vs Data 2)

View 9 Replies View Related

Return Cell Address That Falls Within A Range Of A Custom Lookup Function

Jul 3, 2009

I am performing a lookup using the populare user defined function nlookup, which does not take a lookuparray argument to find lookup values. But I only want to return lookup values that fall into a certain array. To accomplish this, I figured out that I need a way to specify a condition of the type "if cell address of lookup value falls within range"

It should be possible if I find a way to return the cell address of the nlookup value, but as nlookup is not limited by a lookuparray argument, so I was not able to use the address/index/match that come up in similar questions.

View 3 Replies View Related

Adding A Function On A Column

Aug 14, 2007

I have a column which has either EDC or EDT in it, can I add a function to this column which says 'if EDC then output Eau De Toilette' and then Eau De Toilette get's ouputted into a seperate column? Is this kind of thing even possible in Excel?

View 14 Replies View Related

Adding Day In Date Function

Oct 27, 2008

I have the following formula in a cell

=LOOKUP(WEEKDAY(A1),D2:D8,C2:C8)&A1

that I want to look up the Day (mon, tue, etc) and then return the date entered in cell A1

So if the date in cell A1 is 01/05/08, the formula should return Thu01/05/08.
Currently it returns Thu39569, even though the cell is formatted as a date format... How do I get it to return the date in a date format?

View 9 Replies View Related

Adding More Than One Worksheet_Change Function

Feb 1, 2007

I have a bit of code someone on this board provided and I want to make it work for two different ranges. If I just paste it, I get an ambiguous name error. How do I make it work for a second range?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = [A2:A101]
If Intersect(Target, rng) Is Nothing Then Exit Sub
If IsEmpty(Target) Then
Target.Offset(0, 4).Value = ""
Target.Offset(0, 1).Value = ""
Else
Target.Offset(0, 4).Value = Application.UserName
Target.Offset(0, 1).Value = Date
End If
End Sub

View 9 Replies View Related

Adding Time Function

Dec 9, 2008

I want to add two times together.

14:00 (time) + 03:42 (duration) = 17:42

Is there vba code to be able to do this or an excel function?
I have tried searching but strangely couldnt find anything?

View 2 Replies View Related

Adding Another Condition To Sumif Function?

Jan 8, 2014

Below is my function.

="Profit $"&SUM(IF(Sold!D15:D8998<>"",IF(MONTH(Sold!D15:D8998)=A4,Sold!H15:H8998)))

I would like to edit this function to also count profit only if Cell A15:A8998 in my Sold sheet has the words "Shipped". The text value Shipped is not a text value I entered, it is automatically written based on a Vlookup formula I created.

View 2 Replies View Related

Adding Third Criteria To Sumproduct Function

Feb 5, 2014

=IF(LEN(A480),SUMPRODUCT((RIGHT(Sold!$D$15:$D$8998,LEN(A480))=A480)*(MONTH(Sold!$M$15:$M$8998)=12)),"")

I would like to edit the function above and add a third criteria. If Sold!S:S,"Returns" to my sumproduct.

I tried to edit it myself with the function below but it didn't work.

=IF(LEN(A480),SUMPRODUCT((RIGHT(Sold!$D$15:$D$8998,LEN(A480))=A480,Sold!S:S,"Returns")*(MONTH(Sold!$M$15:$M$8998)=12)),"")

View 3 Replies View Related

Adding Space After Period In If Function?

Apr 24, 2012

I have the following IF function in a spreadsheet. =IF(AE2 = "AZ", "We've recently changed our name from Company A to Company B.", "")

How do you Make it so there is a Space at the end of the period?

View 3 Replies View Related

Adding Text To Rank Function

Aug 12, 2008

I am trying to rank a list of numbers, such as:

1
3
5
3
4
1

I have no problem with the rank function in terms of the ties showing as duplicate values, however, when this occurs I would like a "T-" to appear before ranks that are tied, and show nothing if they are not tied. Essentially, I want the final result to look like this, without having to manually add the "T-" after the ranking is complete.
1 T-5 3 T-3 5 1 3 T-3 4 2 1 T-5
I have done more complex Excel formulas before, but for some reason this is stumping me.

View 9 Replies View Related

Extending Sum Function When Adding Rows

Dec 7, 2006

If I have a sum function that adds up:

= SUM(U41:U45)

but I add a row at 41 I now get sum function

=SUM(S42:S46) when I really want it to incorporate the just add row to look like this:

=SUM(U41:U46)

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved