+ Reply to Thread
Results 1 to 16 of 16

VBA Coding for outlook

  1. #1
    Registered User
    Join Date
    12-07-2019
    Location
    Appleton Cheshire
    MS-Off Ver
    Office 365
    Posts
    19

    VBA Coding for outlook

    Hi I'm asking if someone can help me?
    I'm trying to write VBA code to send an email with various cell data, I thought i had it, but i keep getting messages with "invalid use of Property"
    I'm in the early stages training with work to deal with VBA Codes, but this has got me stuck, Please could someone help?

    I've attached my spreadsheet i'm working on and my code in :
    Attached Files Attached Files
    Last edited by Karlos588; 04-19-2020 at 06:46 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: VBA Coding for outlook

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("H12:H23")) Is Nothing Then
    With Target(1, 2, 5)
    .Value = Date
    End With

    With Target(1, 3, 6)

    .Value = Environ("Username")

    End With
    End If
    End Sub


    target is a range object and does not support any parameter as you want to use them
    what do you want to do?

  3. #3
    Registered User
    Join Date
    12-07-2019
    Location
    Appleton Cheshire
    MS-Off Ver
    Office 365
    Posts
    19

    Re: VBA Coding for outlook

    Sorry this is the code i need help with.


    Sub SendEmail_Supportservices()

    Dim EmailApp As Outlook.Application
    Set EmailApp = New Outlook.Application
    Dim EmailItem As Outlook.MailItem
    Set EmailItem = EmailApp.CreateItem(olMailItem)

    EmailItem.To =
    EmailItem.Subject = "Labels for Dispatch" .range ("i5")

    EmailItem.HTMLBody = "I will be bringing some Labels down for dispatch." & "<br>" & "<br>" & "Please Send to the following Address""<br>"
    vbNewLine& _
    Address: .Range ("i7")
    vbNewLine& _
    Contact: .Range ("n3")
    vbNewLine& _
    Telephone: .Range ("n6")
    vbNewLine& _
    .Range ("h26") & .Range("i26") & QTY: .Range ("J26")
    vbNewLine& _
    .Range ("h29") & .Range("i29") & QTY: .Range ("J29")
    vbNewLine& _
    .Range ("h32") & .Range("i32") & QTY: .Range ("J32")
    vbNewLine& _
    Weight: .Range ("E27") & KG
    vbNewLine& _
    Dimensions: W: .Range ("C27") & H: .Range ("c28") & D: .Range ("C29")
    vbNewLine "Kind Regards"


    End Sub

  4. #4
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: VBA Coding for outlook

    you mixed strings with something

    I miss "with" with a worksheet object. without you cannot use ".range"

    Please Login or Register  to view this content.
    Nevertheless you should correct function Private Sub Worksheet_Change !

    cheers

  5. #5
    Registered User
    Join Date
    12-07-2019
    Location
    Appleton Cheshire
    MS-Off Ver
    Office 365
    Posts
    19

    Re: VBA Coding for outlook

    Thanks for that! Its work brilliantly,

    How do i change the "&" to create a space in between the ranges?

    Cheers

  6. #6
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: VBA Coding for outlook

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    12-07-2019
    Location
    Appleton Cheshire
    MS-Off Ver
    Office 365
    Posts
    19

    Re: VBA Coding for outlook

    Hi if these cells are blanks how do i get it to not transfer any data across?

    "<br>" & ws.Range("h26") & ws.Range("i26") & " QTY: " & ws.Range("J26") & _
    "<br>" & ws.Range("h29") & ws.Range("i29") & " QTY: " & ws.Range("J29") & _
    "<br>" & ws.Range("h32") & ws.Range("i32") & " QTY: " & ws.Range("J32") & _

    Thank you again for your help!

  8. #8
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: VBA Coding for outlook

    Assuming that non of the cells have valid data if the first is empty then use
    Please Login or Register  to view this content.
    cheers
    Last edited by gue2013; 04-20-2020 at 01:45 AM.

  9. #9
    Registered User
    Join Date
    12-07-2019
    Location
    Appleton Cheshire
    MS-Off Ver
    Office 365
    Posts
    19

    Re: VBA Coding for outlook

    Hi the rell cell ranges may not always have cell data in them, if this is the case i dont want the left over text "reels of" "&" "for" being copied across, is there away of doing this?

    "Hi Support Services<br><br> I will be bringing some" & " " & ws.Range("J11") & " " & "Labels down for dispatch." & "<br>" & _
    "<br><tr><td><b>Company:</b></td><td> " & ws.Range("i3") & _
    "<br><tr><td><b>Address:</b></td><td> " & ws.Range("i7") & _
    "<br><tr><td><b>Contact:</b></td><td> " & ws.Range("n3") & _
    "<br><tr><td><b>Tel:</b></td><td> " & ws.Range("n6") & "<br>" & _
    "<br><tr><td><b><u>Description</u></b></td><td>" & _
    "<br><tr><td><b>Scheme:</b></td><td>" & " " & ws.Range("i6") & _
    "<br>" & ws.Range("j26") & " " & "Reels of" & " " & ws.Range("k26") & " " & ws.Range("J11") & " " & "for" & " " & ws.Range("h26") & " " & ws.Range("i26") & _
    "<br>" & ws.Range("j27") & " " & "Reels of" & " " & ws.Range("k27") & " " & ws.Range("J11") & " " & "for" & " " & ws.Range("h26") & " " & ws.Range("i27") & _
    "<br>" & ws.Range("j28") & " " & "Reels of" & " " & ws.Range("k28") & " " & ws.Range("J11") & " " & "for" & " " & ws.Range("h26") & " " & ws.Range("i28") & _
    "<br>" & _
    "<br>" & ws.Range("j29") & " " & "Reels of" & " " & ws.Range("k29") & " " & ws.Range("J11") & " " & "for" & " " & ws.Range("h29") & " " & ws.Range("i29") & _
    "<br>" & ws.Range("j30") & " " & "Reels of" & " " & ws.Range("k30") & " " & ws.Range("J11") & " " & "for" & " " & ws.Range("h29") & " " & ws.Range("i30") & _
    "<br>" & ws.Range("j31") & " " & "Reels of" & " " & ws.Range("k31") & " " & ws.Range("J11") & " " & "for" & " " & ws.Range("h29") & " " & ws.Range("i31") & _
    "<br>" & _
    "<br>" & ws.Range("j32") & " " & "Reels of" & " " & ws.Range("k32") & " " & ws.Range("J11") & " " & "for" & " " & ws.Range("h32") & " " & ws.Range("i32") & _
    "<br>" & ws.Range("j32") & " " & "Reels of" & " " & ws.Range("k33") & " " & ws.Range("J11") & " " & "for" & " " & ws.Range("h32") & " " & ws.Range("i33") & _
    "<br>" & ws.Range("j32") & " " & "Reels of" & " " & ws.Range("k34") & " " & ws.Range("J11") & " " & "for" & " " & ws.Range("h32") & " " & ws.Range("i34") & _

    "<br><br><tr><td><b>Weight approx:</b></td><td> " & ws.Range("E27") & "KG" & _
    "<br><br><tr><td><b>Box Dimensions:</b></td><td> W: " & ws.Range("C27") & "cm" & " H:" & ws.Range("c28") & "cm" & " D: " & ws.Range("C29") & "cm" & _
    "<br>" & _
    "<br>" & _
    "Please obtain a receipt and save in the job.<br><br>If you need anything further please do not hesitate to contact me." & _
    "<br>" & _
    "<br>Kind Regards"
    Last edited by Karlos588; 04-20-2020 at 02:25 AM.

  10. #10
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: VBA Coding for outlook

    use iif(condition, TRUE, FALSE) as shown in my previous reply for anything you want to suppress

  11. #11
    Registered User
    Join Date
    12-07-2019
    Location
    Appleton Cheshire
    MS-Off Ver
    Office 365
    Posts
    19

    Re: VBA Coding for outlook

    Hi, How would this be put into the formula, I've tried a few ways and it breaks the formula up?

  12. #12
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: VBA Coding for outlook

    Please Login or Register  to view this content.
    use the iif function
    Please Login or Register  to view this content.
    for each line you want to suppress.
    I've tried a few ways and it breaks the formula up?
    I have no idea why it is not working. you have to provide your code to see whats wrong

  13. #13
    Registered User
    Join Date
    12-07-2019
    Location
    Appleton Cheshire
    MS-Off Ver
    Office 365
    Posts
    19

    Re: VBA Coding for outlook

    I've put the iif in and it has all gone red saying expected expression.


    ewrd = "Hi Support Services<br><br> I will be bringing some" & " " & ws.Range("J11") & " " & "Labels down for dispatch." & "<br>" & _
    "<br><tr><td><b>Company:</b></td><td> " & ws.Range("i3") & _
    "<br><tr><td><b>Address:</b></td><td> " & ws.Range("i7") & _
    "<br><tr><td><b>Contact:</b></td><td> " & ws.Range("n3") & _
    "<br><tr><td><b>Tel:</b></td><td> " & ws.Range("n6") & "<br>" & _
    "<br><tr><td><b><u>Description</u></b></td><td>" & _
    "<br><tr><td><b>Scheme:</b></td><td>" & " " & ws.Range("i6") & _
    IIf(ws.Range("h26") <> "",
    "<br>" & ws.Range("h26") & ws.Range("i26") & " QTY: " & ws.Range("J26") & _
    "<br>" & ws.Range("h29") & ws.Range("i29") & " QTY: " & ws.Range("J29") & _
    "<br>" & ws.Range("h32") & ws.Range("i32") & " QTY: " & ws.Range("J32"), "") & _
    "<br>" & ws.Range("j26") & " " & "Reels of" & " " & ws.Range("k26") & " " & ws.Range("J11") & " " & "for" & " " & ws.Range("h26") & " " & ws.Range("i26") & _
    "<br>" & ws.Range("j27") & " " & "Reels of" & " " & ws.Range("k27") & " " & ws.Range("J11") & " " & "for" & " " & ws.Range("h26") & " " & ws.Range("i27") & _
    "<br>" & ws.Range("j28") & " " & "Reels of" & " " & ws.Range("k28") & " " & ws.Range("J11") & " " & "for" & " " & ws.Range("h26") & " " & ws.Range("i28") & _
    "<br>" & _
    "<br>" & ws.Range("j29") & " " & "Reels of" & " " & ws.Range("k29") & " " & ws.Range("J11") & " " & "for" & " " & ws.Range("h29") & " " & ws.Range("i29") & _
    "<br>" & ws.Range("j30") & " " & "Reels of" & " " & ws.Range("k30") & " " & ws.Range("J11") & " " & "for" & " " & ws.Range("h29") & " " & ws.Range("i30") & _
    "<br>" & ws.Range("j31") & " " & "Reels of" & " " & ws.Range("k31") & " " & ws.Range("J11") & " " & "for" & " " & ws.Range("h29") & " " & ws.Range("i31") & _
    "<br>" & _
    "<br>" & ws.Range("j32") & " " & "Reels of" & " " & ws.Range("k32") & " " & ws.Range("J11") & " " & "for" & " " & ws.Range("h32") & " " & ws.Range("i32") & _
    "<br>" & ws.Range("j32") & " " & "Reels of" & " " & ws.Range("k33") & " " & ws.Range("J11") & " " & "for" & " " & ws.Range("h32") & " " & ws.Range("i33") & _
    "<br>" & ws.Range("j32") & " " & "Reels of" & " " & ws.Range("k34") & " " & ws.Range("J11") & " " & "for" & " " & ws.Range("h32") & " " & ws.Range("i34") & _
    "<br><br><tr><td><b>Weight approx:</b></td><td> " & ws.Range("E27") & "KG" & _
    "<br><br><tr><td><b>Box Dimensions:</b></td><td> W: " & ws.Range("C27") & "cm" & " H:" & ws.Range("c28") & "cm" & " D: " & ws.Range("C29") & "cm" & _
    "<br>" & _
    "<br>" & _
    "Please obtain a receipt and save in the job.<br><br>If you need anything further please do not hesitate to contact me." & _
    "<br>" & _
    "<br>Kind Regards"

  14. #14
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: VBA Coding for outlook

    Please Login or Register  to view this content.
    ==> you forgot the line continuation character "_"
    Please Login or Register  to view this content.
    but now you will run into another problem --> "too many line continuation characters"

    different possibilities to solve it:

    1) you have to define some substrings in which you collect your data and then put them together at the end
    2) or you define a function "CollectLine" where you get the string for one line (rows for j26 to j32 always have the same format) and put then together like
    CollectLine( "J26") & CollectLine( "J27") & ... & CollectLine( "J32")
    3) or you handle it via a for .. next loop


    additional
    Please Login or Register  to view this content.
    is the same as
    Please Login or Register  to view this content.
    Last edited by gue2013; 04-20-2020 at 06:27 AM.

  15. #15
    Registered User
    Join Date
    12-07-2019
    Location
    Appleton Cheshire
    MS-Off Ver
    Office 365
    Posts
    19

    Re: VBA Coding for outlook

    Thanks for all your help with this, Ive been playing around with this for a few hours now and its not liking any off it.
    Would it be easier to change that part of the htmlbody to a range area?

    If so would this be easier to filter the no data areas out?

  16. #16
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: VBA Coding for outlook

    try it this way (you do not need to concatenate all lines, you simple add strings)

    Please Login or Register  to view this content.
    if you put everything in an area this would also be an option. But you have to filter it and then you have to format it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Changing vba coding to outlook.
    By Throughstream in forum Excel General
    Replies: 13
    Last Post: 09-11-2018, 10:32 AM
  2. Outlook to Excel VBA Coding Error
    By eman1234 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-31-2018, 08:16 PM
  3. MS Outlook - VBA Coding Request for Monitoring Ftp Servers
    By ganeshinscribe in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-05-2012, 10:03 AM
  4. MS Outlook - VBA Coding Request for Monitoring Ftp Servers
    By ganeshinscribe in forum Outlook Programming / VBA / Macros
    Replies: 0
    Last Post: 07-05-2012, 05:57 AM
  5. VBA coding for sending email from excel with the help of outlook
    By NASIR KHAN in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-30-2012, 07:17 PM
  6. Coding with Outlook Web App
    By JHCross in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-07-2011, 08:45 AM
  7. Outlook & SubFolders coding
    By MSweetG222 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-05-2006, 08:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1