+ Reply to Thread
Results 1 to 5 of 5

VBA copy content to or screenshot to Thunderbird

  1. #1
    Registered User
    Join Date
    03-18-2016
    Location
    -
    MS-Off Ver
    -
    Posts
    9

    VBA copy content to or screenshot to Thunderbird

    Hi all,

    I'm working on a button that can record down the data in the cells and send them as the body in the email through Mozilla Thunderbird, however, my code limitation is only to able to send a row of data for a time, anyone can help to modify my code so that:

    -Able to detect the row and identify that it is the rows that latest updated and will record the data(only the latest data) and send as email through Mozilla Thunderbird,
    OR
    -Screenshot the whole excel page and embed it into the body of the Mozilla Thunderbird and send out as email

    This is my excel prototype:
    http://www.tiikoni.com/tis/view/?id=2b97413

    Below is my code:

    Sub Thunderbird_mail()
    Dim Toadrs As String, Toadrs1 As String, Toadrs2 As String, Toadrs3 As String, Toadrs4 As String, _
    Ccadrs As String, Ccadrs1 As String, Ccadrs2 As String, Ccadrs3 As String, Ccadrs4 As String, kenmei As String, body As String, TimeStart As Double

    Toadrs = ThisWorkbook.ActiveSheet.Cells(4, 2) 'Cells(Row number,column number)
    Toadrs1 = ThisWorkbook.ActiveSheet.Cells(5, 3)
    Toadrs2 = ThisWorkbook.ActiveSheet.Cells(6, 3)
    Toadrs3 = ThisWorkbook.ActiveSheet.Cells(7, 3)
    Toadrs4 = ThisWorkbook.ActiveSheet.Cells(8, 3)
    Ccadrs = ThisWorkbook.ActiveSheet.Cells(9, 3)
    Ccadrs1 = ThisWorkbook.ActiveSheet.Cells(10, 3)
    Ccadrs2 = ThisWorkbook.ActiveSheet.Cells(11, 3)
    Ccadrs3 = ThisWorkbook.ActiveSheet.Cells(12, 3)
    Ccadrs4 = ThisWorkbook.ActiveSheet.Cells(13, 3)
    kenmei = ThisWorkbook.ActiveSheet.Cells(14, 3) 'Subject
    Title_Name = "Name:"
    Title_Purpose = "Purpose:"
    Title_BookDate = "Book Date:"
    Title_TimeStart = "Time Start:"
    Title_TimeEnd = "Time End:"
    Name = ThisWorkbook.ActiveSheet.Cells(21, 3) 'Name
    Purpose = ThisWorkbook.ActiveSheet.Cells(21, 8) 'Purpose
    BookDate = ThisWorkbook.ActiveSheet.Cells(21, 20) 'BookDate
    TimeStart = ThisWorkbook.ActiveSheet.Cells(21, 21) 'TimeStart
    TimeEnd = ThisWorkbook.ActiveSheet.Cells(21, 23) 'TimeEnd
    link = Replace(link, "\", "/")
    body = Title_Name & Name & "%0a" & Title_Purpose & Purpose & "%0a" & Title_BookDate & BookDate & "%0a" & Title_TimeStart & TimeStart & "%0a" & Title_TimeEnd & TimeEnd & "%0a" ' & "file:///" & link
    'body = honbun & "%0a" & "%0a" & link
    Call CreateMailByThunderbird(Toadrs, Toadrs1, Toadrs2, Toadrs3, Toadrs4, Ccadrs, Ccadrs1, Ccadrs2, Ccadrs3, Ccadrs4, kenmei, body)
    End Sub
    Sub CreateMailByThunderbird(Toadrs As String, Toadrs1 As String, Toadrs2 As String, Toadrs3 As String, Toadrs4 As String, _
    Ccadrs As String, Ccadrs1 As String, Ccadrs2 As String, Ccadrs3 As String, Ccadrs4 As String, kenmei As String, body As String)
    Dim sPath, encodedkenmei
    With CreateObject("ScriptControl")
    .Language = "JScript"
    encodedkenmei = .CodeObject.encodeURI(kenmei)
    End With
    If Application.OperatingSystem = "Windows (32-bit) NT 6.01" Then

    sPath = """C:\Program Files\Mozilla Thunderbird\Thunderbird.exe"" -compose "

    Else

    sPath = """ C:\Program Files (x86)\Mozilla Thunderbird\Thunderbird.exe"" -compose "

    End If
    arg = "mailto:" & Toadrs & ";" & Toadrs1 & ";" & Toadrs2 & ";" & Toadrs3 & ";" & Toadrs4 & "?" & _
    "cc=" & Ccadrs & ";" & Ccadrs1 & ";" & Ccadrs2 & ";" & Ccadrs3 & ";" & Ccadrs4 & "&" & "subject=" & encodedkenmei & "&" & "body=" & body
    Shell sPath & arg
    Sleep 1000
    CreateObject("Wscript.Shell").SendKeys "^{enter}", True
    End Sub
    Attached Images Attached Images
    Last edited by zsparrow; 04-11-2016 at 03:41 AM.

  2. #2
    Registered User
    Join Date
    03-18-2016
    Location
    -
    MS-Off Ver
    -
    Posts
    9

    Re: VBA copy content to or screenshot to Thunderbird

    Bump,

    Any Expert figured this out? Any help is much appreciated.

  3. #3
    Forum Contributor
    Join Date
    06-02-2015
    Location
    delhi
    MS-Off Ver
    2010
    Posts
    104

    Re: VBA copy content to or screenshot to Thunderbird

    Generally I have no idea how to deal with Thunderbird whether it supports table format or Not,I did this trick in Outlook only because there Excel helps you to create Html body with table content.

    What It seems to me possible is you can easily Combine Rows with Looping with VBnewline character and Then using your same code to send an email, I don't think so it will get pasted like a table but it will show u pasted like table without borders.(Rest of things depends on thunderbird)

  4. #4
    Registered User
    Join Date
    03-18-2016
    Location
    -
    MS-Off Ver
    -
    Posts
    9
    Quote Originally Posted by Mandeep Baluja View Post
    Generally I have no idea how to deal with Thunderbird whether it supports table format or Not,I did this trick in Outlook only because there Excel helps you to create Html body with table content.

    What It seems to me possible is you can easily Combine Rows with Looping with VBnewline character and Then using your same code to send an email, I don't think so it will get pasted like a table but it will show u pasted like table without borders.(Rest of things depends on thunderbird)
    First of everything, Thanks for looking into my problem!
    It don't mind if the table is with border or not, as long as the data are able to copy into the body of mail, but I not sure how to deal with looping with the solution you suggest, is it possible if you can lead me further? It will definitely helps. Thanks again!

  5. #5
    Forum Contributor
    Join Date
    06-02-2015
    Location
    delhi
    MS-Off Ver
    2010
    Posts
    104

    Re: VBA copy content to or screenshot to Thunderbird

    What is the criteria for Rows need to be copied and pasted in thunderbird, Latest updated Rows in what sense ?? As with this snapshot I am not able to understand.

+ 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. Macro needed: Capture Screenshot and email using Outlook
    By skultety in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-10-2016, 09:26 AM
  2. Email a screenshot of a specifc row in an email to a specfic email address
    By Cobra75007 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-19-2015, 10:36 AM
  3. copy the content of the email body from outlook to excel sheet
    By Megatronixs in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-17-2014, 03:25 AM
  4. Copy formatted cell content to email body
    By Alsiro in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-02-2014, 04:14 PM
  5. User form screenshot and email in Lotus notes
    By Raanan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-21-2013, 07:06 AM
  6. Trying to get an screenshot and mail it to an email adress
    By basmienis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-12-2012, 05:26 AM

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