Post by Norman JonesCiao Marco,
Post by Marco AugustoSalve a tutti
ho questa semplice macro per inviare email con thunderbird da un foglio excel
Se il Range del testo email è una cella va tutto bene (esempio "A3")
Viceversa se il Range è come da esempio "A3:C16" da errore
Qualcuno sa e può aiutarmi ?
Grazie in anticipo per la collaborazione
Sub sendmail()
Dim BodyMsg As String, Indirizzo As String, Oggetto As String
BodyMsg = Range("A3:C16").Value
Indirizzo = Range("A1").Value
Oggetto = Range("A2").Value
Shell "C:\Program Files (x86)\Mozilla Thunderbird\thunderbird -compose " _
& Chr$(34) & "to='" & Indirizzo & "',subject='" & Oggetto & "',body='" & BodyMsg _
& Chr$(34), vbNormalFocus
Application.Wait Now + TimeValue("00:00:03")
SendKeys "^{ENTER}"
End Sub
www.experts-exchange.com/questions/26517936/vba-to-send-excel-range-by-email-through-thunderbird.html
'=========>>
Option Explicit
'--------->>
Public Sub tbSendMaster()
Dim WB As Workbook
Dim SH As Worksheet
Dim RngBodyMsg As Range, RngIndirizzo As Range
Dim RngOggetto As Range
Dim sIndirizzo As String, sOggetto As String
Const sFoglio As String = "Foglio1"
Const sIntervalloBodyMsg As String = "A3:C16"
Const sIntervalloIndirizzo As String = "A1"
Const sIntervalloOggetto As String = "A2"
Const sIntestazioneBody As String = "Vede il seguenti dati"
Set WB = ThisWorkbook
Set SH = WB.Sheets(sFoglio)
With SH
Set RngBodyMsg = .Range(sBodyMsg)
Set RngIndirizzo = .Range(sIndirizzo)
Set RngOggetto = .Range(sOggetto)
End With
sIndirizzo = RngIndirizzo.Value
sOggetto = RngOggetto.Value
TB_MailSend sIndirizzo, sOggetto, sIntestazioneBody _
& "%0D%0A" & "%0D%0A" _
& rng2text(RngBodyMsg) _
& "%0D%0A" & "Endex"
End Sub
'--------->>
Public Function TB_MailSend(strTo As String, strSubject As String,
strBody As String)
Dim str As String
str = "C:\Program Files\Mozilla Thunderbird\Thunderbird"
str = str & " -compose " & Chr(34) & "mailto:" & strTo & "?"
str = str & "subject=" & Chr(34) & strSubject & Chr(34) & "&"
str = str & "body=" & Chr(34) & strBody & Chr(34)
Call Shell(str, vbNormalFocus)
End Function
'--------->>
Public Function rng2text(Rng As Range) As String
Dim arr As Variant
Dim lngRows As Long
Dim intCols As Integer
Dim rowCount As Long
Dim colCount As Integer
Dim colSize As Variant
Dim strLen As Long
arr = Rng
lngRows = UBound(arr, 1)
intCols = UBound(arr, 2)
ReDim colSize(intCols)
For rowCount = 1 To lngRows
For colCount = 1 To intCols
If Len(arr(rowCount, colCount)) + 5 > colSize(colCount) Then
colSize(colCount) = Len(arr(rowCount, colCount)) + 5
End If
Next ColCount
Next RowCount
For rowCount = 1 To lngRows
For colCount = 1 To intCols
strLen = colSize(colCount) - Len(arr(rowCount, colCount))
rng2text = rng2text & arr(rowCount, colCount) _
& String(strLen, Chr(160))
Next
rng2text = rng2text & "%0D%0A"
Next rowCount
End Function
'<<=========
===
Regards,
Norman
Ciao Marco,
Post by Marco AugustoSalve a tutti
ho questa semplice macro per inviare email con thunderbird da un foglio excel
Se il Range del testo email è una cella va tutto bene (esempio "A3")
Viceversa se il Range è come da esempio "A3:C16" da errore
Qualcuno sa e può aiutarmi ?
Grazie in anticipo per la collaborazione
Sub sendmail()
Dim BodyMsg As String, Indirizzo As String, Oggetto As String
BodyMsg = Range("A3:C16").Value
Indirizzo = Range("A1").Value
Oggetto = Range("A2").Value
Shell "C:\Program Files (x86)\Mozilla Thunderbird\thunderbird -compose " _
& Chr$(34) & "to='" & Indirizzo & "',subject='" & Oggetto & "',body='" & BodyMsg _
& Chr$(34), vbNormalFocus
Application.Wait Now + TimeValue("00:00:03")
SendKeys "^{ENTER}"
End Sub
www.experts-exchange.com/questions/26517936/vba-to-send-excel-range-by-email-through-thunderbird.html
'=========>>
Option Explicit
'--------->>
Public Sub tbSendMaster()
Dim WB As Workbook
Dim SH As Worksheet
Dim RngBodyMsg As Range, RngIndirizzo As Range
Dim RngOggetto As Range
Dim sIndirizzo As String, sOggetto As String
Const sFoglio As String = "Foglio1"
Const sIntervalloBodyMsg As String = "A3:C16"
Const sIntervalloIndirizzo As String = "A1"
Const sIntervalloOggetto As String = "A2"
Const sIntestazioneBody As String = "Vede il seguenti dati"
Set WB = ThisWorkbook
Set SH = WB.Sheets(sFoglio)
With SH
Set RngBodyMsg = .Range(sBodyMsg)
Set RngIndirizzo = .Range(sIndirizzo)
Set RngOggetto = .Range(sOggetto)
End With
sIndirizzo = RngIndirizzo.Value
sOggetto = RngOggetto.Value
TB_MailSend sIndirizzo, sOggetto, sIntestazioneBody _
& "%0D%0A" & "%0D%0A" _
& rng2text(RngBodyMsg) _
& "%0D%0A" & "Endex"
End Sub
'--------->>
Public Function TB_MailSend(strTo As String, strSubject As String,
strBody As String)
Dim str As String
str = "C:\Program Files\Mozilla Thunderbird\Thunderbird"
str = str & " -compose " & Chr(34) & "mailto:" & strTo & "?"
str = str & "subject=" & Chr(34) & strSubject & Chr(34) & "&"
str = str & "body=" & Chr(34) & strBody & Chr(34)
Call Shell(str, vbNormalFocus)
End Function
'--------->>
Public Function rng2text(Rng As Range) As String
Dim arr As Variant
Dim lngRows As Long
Dim intCols As Integer
Dim rowCount As Long
Dim colCount As Integer
Dim colSize As Variant
Dim strLen As Long
arr = Rng
lngRows = UBound(arr, 1)
intCols = UBound(arr, 2)
ReDim colSize(intCols)
For rowCount = 1 To lngRows
For colCount = 1 To intCols
If Len(arr(rowCount, colCount)) + 5 > colSize(colCount) Then
colSize(colCount) = Len(arr(rowCount, colCount)) + 5
End If
Next ColCount
Next RowCount
For rowCount = 1 To lngRows
For colCount = 1 To intCols
strLen = colSize(colCount) - Len(arr(rowCount, colCount))
rng2text = rng2text & arr(rowCount, colCount) _
& String(strLen, Chr(160))
Next
rng2text = rng2text & "%0D%0A"
Next rowCount
End Function
'<<=========
===
Regards,
Norman
Grazie Norman per la pronta risposta; avevo già letto quel post e avevo provato ma non funzionava correttamente.
Ho provato anche la tua versione ho dichiarato la variabile che il debug dava non dichiarata sBodyMsg: