Using a macro to select a row of text and email it...

[pontybiker]

Hi,

If possible, I would like the ability to select text from an excel workbook and email it to an email address. The email address would be dependent on what text is in a certain cell.

This is an example of the workbook containing the data (workbook A):

Sport	£1	£2	AAAA
Education	£2	£2	BBBB
Managment	£6	£1	CCCC
Art	£4	£3	DDDD

<tbody>
</tbody>

This is an example of the email workbook (workbook B);

Sport	j@j.com
Education	b@j.com
Management	g@j.com
Art	k@j.com

<tbody>
</tbody>

So the code would need to email the row of text in workbook A to the email address in workbook B by matching the text in column A in workbook A to the correct email address in column B in workbook B.

I hope this makes sense. I realise this may be asking a lot. As always, I honestly do appreciate anyone's help with these questions.

Kind regards,

J.

 

[Fishboy]

Hi,

If possible, I would like the ability to select text from an excel workbook and email it to an email address. The email address would be dependent on what text is in a certain cell.

This is an example of the workbook containing the data (workbook A):

Sport	£1	£2	AAAA
Education	£2	£2	BBBB
Managment	£6	£1	CCCC
Art	£4	£3	DDDD

<tbody>
</tbody>

This is an example of the email workbook (workbook B);

Sport	j@j.com
Education	b@j.com
Management	g@j.com
Art	k@j.com

<tbody>
</tbody>

So the code would need to email the row of text in workbook A to the email address in workbook B by matching the text in column A in workbook A to the correct email address in column B in workbook B.

I hope this makes sense. I realise this may be asking a lot. As always, I honestly do appreciate anyone's help with these questions.

Kind regards,

J.

Hi pontybiker,

It wasn't entirely clear whether you meant 2 completely separate workbooks (as in separate files) or separate sheets within a single workbook. The following code assumes we are dealing with 2 sheets in a single workbook. It basically looks at whatever row you have selected on Sheet1 to figure out which email address it needs to look up from Sheet2, then looks up the email address, creates an email and mails that row of data to the corresponding email recipient.

Sub MailWithLookup()
' Defines variables
Dim ws1 As Worksheet, ws2 As Worksheet, FindString As String, EmailAdd As String


' Set ws1 as Sheet1 (amend as required)
Set ws1 = Sheets("Sheet1")
' Set ws2 as Sheet2 (amend as required)
Set ws2 = Sheets("Sheet2")
' Set variable FindString as the contents of ws1 column A of the active row
FindString = ws1.Range("A" & ActiveCell.Row).Value
' Set variable EmailAdd as the correspinding email address based on the FindString on ws2
EmailAdd = Application.WorksheetFunction.VLookup(FindString, ws2.Range("A1:B4"), 2, False)


        ' If EmailAdd is not blank then
        If EmailAdd <> "" Then
            ' Select the range of cells on the ws1 as this is what will be emailed
            ws1.Range("A" & ActiveCell.Row, "D" & ActiveCell.Row).Select
            ' Show the envelope on the ActiveWorkbook
            ActiveWorkbook.EnvelopeVisible = True
            With ActiveSheet.MailEnvelope
                ' Set the optional introduction fields thats adds
               .Introduction = "This is what will be in the opening of your email"
               ' Send to the email address in EmailAdd
               .Item.To = EmailAdd
               ' Define your desired subject line
               .Item.Subject = "Your chosen email subject"
               ' Send the email
               .Item.Send
            End With
        End If


End Sub

If you need this code changed so that it actually deals with 2 separate files then let me know the workbook names and the corresponding filepaths and I will try to doctor my code to accommodate them.

 

[pontybiker]

Hi pontybiker,

It wasn't entirely clear whether you meant 2 completely separate workbooks (as in separate files) or separate sheets within a single workbook. The following code assumes we are dealing with 2 sheets in a single workbook. It basically looks at whatever row you have selected on Sheet1 to figure out which email address it needs to look up from Sheet2, then looks up the email address, creates an email and mails that row of data to the corresponding email recipient.

Sub MailWithLookup()
' Defines variables
Dim ws1 As Worksheet, ws2 As Worksheet, FindString As String, EmailAdd As String


' Set ws1 as Sheet1 (amend as required)
Set ws1 = Sheets("Sheet1")
' Set ws2 as Sheet2 (amend as required)
Set ws2 = Sheets("Sheet2")
' Set variable FindString as the contents of ws1 column A of the active row
FindString = ws1.Range("A" & ActiveCell.Row).Value
' Set variable EmailAdd as the correspinding email address based on the FindString on ws2
EmailAdd = Application.WorksheetFunction.VLookup(FindString, ws2.Range("A1:B4"), 2, False)


        ' If EmailAdd is not blank then
        If EmailAdd <> "" Then
            ' Select the range of cells on the ws1 as this is what will be emailed
            ws1.Range("A" & ActiveCell.Row, "D" & ActiveCell.Row).Select
            ' Show the envelope on the ActiveWorkbook
            ActiveWorkbook.EnvelopeVisible = True
            With ActiveSheet.MailEnvelope
                ' Set the optional introduction fields thats adds
               .Introduction = "This is what will be in the opening of your email"
               ' Send to the email address in EmailAdd
               .Item.To = EmailAdd
               ' Define your desired subject line
               .Item.Subject = "Your chosen email subject"
               ' Send the email
               .Item.Send
            End With
        End If


End Sub

If you need this code changed so that it actually deals with 2 separate files then let me know the workbook names and the corresponding filepaths and I will try to doctor my code to accommodate them.

Thanks for replying Fishboy. It always amazes me that people take time out of there day to help me.

If possible could you change it so that is uses two separate worksheets? They are called Telephone-Charges and emaillist.

Once again, I can't thank you enough for this.

J.

 

[Fishboy]

Thanks for replying Fishboy. It always amazes me that people take time out of there day to help me.

You are most welcome mate

If possible could you change it so that is uses two separate worksheets? They are called Telephone-Charges and emaillist.

2 completely separate documents? Or are these 2 sheets within the same document?

If they are in separate documents, can you let me know the file extension of the emaillist workbook? It is a .xlsx, .xls, .xlsm, or something else? Also if they are in separate documents I will also need to know the corresponding sheet names in each document.

 

[pontybiker]

Hi Fishboy,

For testing purposes I copied the email workbook into sheet 2. I've run the code and I'm getting the following "run-time error '1004' - Unable to get the VLookup property of the WorksheetFunction class"

J.

 

[pontybiker]

The are two completely separate documents, one is called Telephone-Charges.xlsx and the other is emaillist.xlsx.

Thank you so much.

Jamie.

 

[Fishboy]

Hi Fishboy,

For testing purposes I copied the email workbook into sheet 2. I've run the code and I'm getting the following "run-time error '1004' - Unable to get the VLookup property of the WorksheetFunction class"

J.

Hmm, curious. You can download my test document HERE. You will obviously need to put real email addresses in column B on sheet 2 before testing.

Does my test document work for you?

 

[pontybiker]

That seemed to work!

 

[pontybiker]

Hi Fishboy,

I've managed to get it to work with my workbook now as well. Thank you so so much. I hate asking you for further help. I was wondering if we can get this to work with separate workbooks next?

If possible the only other amendment I would love to make is have it automatically select search Column A in spreadsheet A for the corresponding text in column A in workbook B and if it matches send the text in the corresponding row in workbook A in an email.

Basically if don't want to have to select the text before I run the code. Does that make sense?

I apologies if I'm asking too much. Thank you for everything you have done so far.

 

[Fishboy]

Hi Fishboy,

I've managed to get it to work with my workbook now as well. Thank you so so much. I hate asking you for further help. I was wondering if we can get this to work with separate workbooks next?

If possible the only other amendment I would love to make is have it automatically select search Column A in spreadsheet A for the corresponding text in column A in workbook B and if it matches send the text in the corresponding row in workbook A in an email.

Basically if don't want to have to select the text before I run the code. Does that make sense?

I apologies if I'm asking too much. Thank you for everything you have done so far.

Sorry mate, been out on my lunch break so have only just seen your replies. Leave it with me and I will see if I can tweak it accordingly to work with 2 separate workbooks.

With regards to the second part of your query, do you mean you want it to go through each cell in workbook1 column A and if there is a corresponding email address then send a mail to it? That means you dont need to select a row first it will work its way through all of them?