1

I currently have a spreadsheet that pulls information out from another sheet. It uses a series of Vlookup formulas to generate a message using the client's details in Sheet1. To send these emails I have been using two macro commands. One simply copies the range (B4:L36): 

Sub Copy()
Copy Macro
Range("B4:L36").Select
Selection.Copy
End Sub

The other command opens a blank email: 

Sub MailIt()
Dim oMailItem As Object
Dim oOLapp As Object
Set oOLapp = CreateObject("Outlook.application")
Set oMailItem = oOLapp.CreateItem(0)
With oMailItem
.To = ""
.CC = ""
.Subject = ""
.Body = ""
.Display
End With
Set oOLapp = Nothing
Set oMailItem = Nothing
End Sub

I then paste the message as a picture in the email body. I go back to my spreadsheet and copy the generated title from the range E1:J1 and paste this as my email subject. Lastly, I copy the recipient address from L2 and paste it into my email and send.

Is there a way to have a VBA command that:

  1. looks at the address in L2 and pastes it as the recipient address in the email (assuming that the VLookup formula would not interfere with this).
  2. copies the title range from E1:J1 and pastes it as the email subject.
  3. copies the message range (B4:L36) and pastes it as the email body (as a picture).

As a visual example of what the spreadsheet is doing please see the hyperlink. The basic idea is that it is taking the details from the INFO tab and using the VLookup formulas to generate the message in the Proforma tab (for data protective reasons, I've blacked out the message). If there are better methods of doing this, please let me know - I'm always happy to learn more!

I have done a lot of research into this and have found that it is possible to create emails that do this, however, I am struggling to get it to work and would appreciate some help!

Thank you.

braX
  • 11,506
  • 5
  • 20
  • 33
ToySoldier
  • 25
  • 5
  • can you simply replace `.To = ""` with `.To = Range("L2").Value`? As well, `.Subject = Range("E1").Value&Range("F1").Value&Range("G1").Value...etc."`? The picture seems more complicated, and is answered [here](https://stackoverflow.com/questions/44869790/embed-picture-in-outlook-mail-body-excel-vba) – SRT HellKitty Dec 06 '18 at 03:56
  • I am getting an 'error mismatch' when using that format. Do you have any ideas why? – ToySoldier Dec 06 '18 at 21:16
  • I see you have a solution, however I sill still answer this. the type must be a string, however the value in the cell may be a number of some sort(int, long, etc.) so you can use [vartype](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/vartype-function) to confirm it is a string. You can also use `.Text` instead of `.Value.` in the code. – SRT HellKitty Dec 07 '18 at 14:56
  • Apologies for the delayed reply. Thanks for that, the To and Subject address is working as intended - although I'm getting a syntax error with the &Range aspect. Is there an easier method of copying the Excel range specified in to the email body, like the solution below? – ToySoldier Dec 15 '18 at 12:58

1 Answers1

1

whenever I need to send a part of a spreadsheet, I usually do that directly via Excel, not including Outlook:

Sub SendTheStuff()
    Dim strRecipients As String
    Dim strSubject As String

    strRecipients = Worksheets("Sheet 1").Cells(2, 12).Value   'your client's mail adress, assuming it to be on Sheet 1 of your book, adjust to your needs
    strSubject = Worksheets("Sheet 1").Cells(1, 5).Value   'The content of Sheet 1, Cell "E1" - see if you can combine the range you want into one cell to keep code simple - and adjust the Sheet if necessary

    Worksheets("Sheet 1").Range("B4:L36").Select
    With Selection
        ActiveWorkbook.EnvelopeVisible = True
        With ActiveSheet.MailEnvelope
            .Item.Subject = strSubject
            .Item.To = strRecipients
            .Item.Attachments.Add "T:\he\Path\To\An\Attachm.ent"   'you may even add a file in case you need to
            .Item.Send
        End With
    End With

ActiveWorkbook.EnvelopeVisible = False

End Sub

This method uses Excel as the mail client, works fine for me - give it a try!

EarlyBird2
  • 296
  • 2
  • 14
  • That method is so much cleaner, I didn't know that was possible! I did manage to get it partially working, the email body is working perfectly but I am struggling to get the recipient and the subject section to work. Is it because the cell value is a formula that it is creating the error? I've included an image to the original post, if it makes it more clearer what I am trying to do. But, thanks, that is already an amazing step in the right direction!! – ToySoldier Dec 06 '18 at 21:33
  • From all I can see, e.g. `.Cells(2, 12).Value`should return the result of the formula. Just tried: String is filled as it should be. What do you see if you add `debug.print strRecipients`and `debug.print strSubject`right after the lines where you fill the variables? – EarlyBird2 Dec 07 '18 at 04:57
  • Awesome, that seems to have done it! Two quick questions though, when I remove ".Item.Attachments", the code doesn't work, how can I remove this without crashing the code? Also, is it possible to import the user's signatures in excel or default all messages with that user's signature? – ToySoldier Dec 07 '18 at 18:15
  • Did you remove the whole line of code, not just `.Item.Attachments`? Should work then. Never tried adding a signature, and not sure whether that would work either. But as a workaround, you might at least include the signature text in the selected cells. Not ideal, but feasable. Glad it worked for you so far. If you are fine with the answer, accepting it would be much appreciated. – EarlyBird2 Dec 10 '18 at 04:55
  • That worked perfectly, thank you. I've been thinking of a way around the signature text. If I use a code to identify the user when they input in to a certain cell (it finds their windows user name), I could then use another vLookup formula to change the signatures depending on the user. I could then extend the range so that it sends the signature along with the body of text. How does that sound, is it to complex? – ToySoldier Dec 15 '18 at 13:03
  • Should be feasable. – EarlyBird2 Dec 17 '18 at 08:15