Excel VBA to Attach File And Automatically Send Email

[br.giri68]

Hello,

I have been using VBA Codes to create Macros. But, with my current scenario i have no idea as how to do it.


Scenario :- I have to send reports to our Client , with my updated Report (which will be saved on Desktop).

You can Find Email Address in the Column " AA" in the Report .

I want the Macro to Run and then to attach file and Also, it should pick " Email address " automatically. If possible, a Pop-up to ask User " do you want to send email".

Your Help is Much Appreciated.

Thanks.

Giri

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1 (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg (https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg)
https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg (https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9 (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I)
https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3 (https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
ttps://www.youtube.com/watch?v=LP9fz2DCMBE (ttps://www.youtube.com/watch?v=LP9fz2DCMBE)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8 (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8)
ttps://www.youtube.com/watch?v=bFxnXH4-L1A (ttps://www.youtube.com/watch?v=bFxnXH4-L1A)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg)
ttps://www.youtube.com/watch?v=GqzeFYWjTxI (ttps://www.youtube.com/watch?v=GqzeFYWjTxI)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Friel300]

E-Mailing from excel

hey this is a piece from my refereence file
it should work pretty well.

it should all be pretty straight forward to taylor to your needs.


Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "Hi there" & vbNewLine & vbNewLine & _
"Cell A1 is changed" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"

On Error Resume Next
With OutMail
.To = Email@Domain.com 'Email Address (can be a cell reference)
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = strbody
'.Attachments.Add ("C:\test.txt") 'You can add a file like this
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub
any problems give me a shout =D

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg (https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg)
https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=Ugz39PGfytiMUCmTPTl4AaABAg.91d_Pbzklsp9zfGbIr8h gW (https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=Ugz39PGfytiMUCmTPTl4AaABAg.91d_Pbzklsp9zfGbIr8h gW)
https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=UgwbcybM8fXnaIK-Y3B4AaABAg.97WIeYeaIeh9zfsJvc21iq (https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=UgwbcybM8fXnaIK-Y3B4AaABAg.97WIeYeaIeh9zfsJvc21iq)
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg.9zaUSUoUUYs9zciSZa95 9d (https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg.9zaUSUoUUYs9zciSZa95 9d)
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg.9zaUSUoUUYs9zckCo1tv PO (https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg.9zaUSUoUUYs9zckCo1tv PO)
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgwMsgdKKlhr2YPpxXl4AaABAg (https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgwMsgdKKlhr2YPpxXl4AaABAg)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg.9xmkXGSciKJ9xonTti2s Ix (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg.9xmkXGSciKJ9xonTti2s Ix)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg.9xnskBhPnmb9xoq3mGxu _b (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg.9xnskBhPnmb9xoq3mGxu _b)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg.9xm_ufqOILb9xooIlv5P LY (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg.9xm_ufqOILb9xooIlv5P LY)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg.9xmt8i0IsEr9y3FT9Y9F eM (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg.9xmt8i0IsEr9y3FT9Y9F eM)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Friel300]

oh sorry and the question would go like


if msgbox("Do you want to send an e-mail?",vbQuestion+vbYesNo,"Send Email" ) = vbNo then exit sub

at the start of the code.

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://eileenslounge.com/viewtopic.php?p=316441#p316441 (https://eileenslounge.com/viewtopic.php?p=316441#p316441)
https://eileenslounge.com/viewtopic.php?p=324736#p324736 (https://eileenslounge.com/viewtopic.php?p=324736#p324736)
https://eileenslounge.com/viewtopic.php?p=324990#p324990 (https://eileenslounge.com/viewtopic.php?p=324990#p324990)
https://eileenslounge.com/viewtopic.php?f=27&t=41937&p=325485#p325485 (https://eileenslounge.com/viewtopic.php?f=27&t=41937&p=325485#p325485)
https://eileenslounge.com/viewtopic.php?p=325609#p325609 (https://eileenslounge.com/viewtopic.php?p=325609#p325609)
https://eileenslounge.com/viewtopic.php?p=325610#p325610 (https://eileenslounge.com/viewtopic.php?p=325610#p325610)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGADdPM65i9 PG (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGADdPM65i9 PG)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGADdPQHFk_ zm (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGADdPQHFk_ zm)
http://www.eileenslounge.com/viewtopic.php?p=324457#p324457 (http://www.eileenslounge.com/viewtopic.php?p=324457#p324457)
http://www.eileenslounge.com/viewtopic.php?p=324064#p324064 (http://www.eileenslounge.com/viewtopic.php?p=324064#p324064)
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960 (http://www.eileenslounge.com/viewtopic.php?p=323960#p323960)
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg (https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg)
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg.ADd4m2zp_xDADd6Nnotj 1C (https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg.ADd4m2zp_xDADd6Nnotj 1C)
s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg (s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg)
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959 (http://www.eileenslounge.com/viewtopic.php?p=323959#p323959)
http://www.eileenslounge.com/viewtopic.php?f=30&t=41784 (http://www.eileenslounge.com/viewtopic.php?f=30&t=41784)
http://www.eileenslounge.com/viewtopic.php?p=323966#p323966 (http://www.eileenslounge.com/viewtopic.php?p=323966#p323966)
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959 (http://www.eileenslounge.com/viewtopic.php?p=323959#p323959)
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960 (http://www.eileenslounge.com/viewtopic.php?p=323960#p323960)
http://www.eileenslounge.com/viewtopic.php?p=323894#p323894 (http://www.eileenslounge.com/viewtopic.php?p=323894#p323894)
http://www.eileenslounge.com/viewtopic.php?p=323843#p323843 (http://www.eileenslounge.com/viewtopic.php?p=323843#p323843)
https://www.youtube.com/watch?v=fEHKPhJxgBA&lc=Ugxx8_MjhC9FDaQgcHN4AaABAg (https://www.youtube.com/watch?v=fEHKPhJxgBA&lc=Ugxx8_MjhC9FDaQgcHN4AaABAg)
https://www.youtube.com/watch?v=jpjYm4UvyWk&lc=Ugx_Qd4rfAN_ZYcJbo94AaABAg.ACGbG9c76OWACGbjKa7H 8k (https://www.youtube.com/watch?v=jpjYm4UvyWk&lc=Ugx_Qd4rfAN_ZYcJbo94AaABAg.ACGbG9c76OWACGbjKa7H 8k)
https://www.youtube.com/watch?v=jpjYm4UvyWk&lc=Ugx_Qd4rfAN_ZYcJbo94AaABAg (https://www.youtube.com/watch?v=jpjYm4UvyWk&lc=Ugx_Qd4rfAN_ZYcJbo94AaABAg)
https://www.youtube.com/watch?v=GyPHaydeng0&lc=UgzE4a4f_e_y9Rk5OR94AaABAg (https://www.youtube.com/watch?v=GyPHaydeng0&lc=UgzE4a4f_e_y9Rk5OR94AaABAg)
https://www.youtube.com/watch?v=I5FkNG94BcQ&lc=UgxXnkEHqulXSR5tXwh4AaABAg (https://www.youtube.com/watch?v=I5FkNG94BcQ&lc=UgxXnkEHqulXSR5tXwh4AaABAg)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa6BSa17 3Z (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa6BSa17 3Z)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa6-64Xpgl (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa6-64Xpgl)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa5ms39y jd (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa5ms39y jd)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa5ZXJwR CM (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa5ZXJwR CM)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa4Pr15N Ut (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa4Pr15N Ut)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa4I83Je lY (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa4I83Je lY)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGADdMo2n-hyF (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGADdMo2n-hyF)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[malay]

E-Mailing from excel

hey this is a piece from my refereence file
it should work pretty well.

it should all be pretty straight forward to taylor to your needs.


Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "Hi there" & vbNewLine & vbNewLine & _
"Cell A1 is changed" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"

On Error Resume Next
With OutMail
.To = Email@Domain.com 'Email Address (can be a cell reference)
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = strbody
'.Attachments.Add ("C:\test.txt") 'You can add a file like this
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub
any problems give me a shout =D

This is quite good but what i need is to forward all mails saved in a folder to a different recipient with the attachments on the mails.I find it difficult to do and the above solution did not serve the purpose as attachments are sent from the hard drive location and not from the saved mails. Please help me to forward all mails in a saved folder with attachments if any.

[Friel300]

you could add the mail that you want to forward to the attachments.

[malay]

Hi,

Thank you for the reply...but let me expand the question for your better understanding.

I have copied some mails from outlook inbox to a folder. Some mails are with attachments and some are without. I need to automate it with excel vba to forward those mails to an email ID that i provide in one cell of excel sheet. I need to forward the mails to keep the format and attachments unchanged. Please help me to solve the problem as it's a project I am doing and having problem solving it on time.

Regards,
Malay

[Excel Fox]

So are you saying that you want to open the saved mails from the hard disk folder, and then just forward those to certain mail IDs?

[Friel300]

If you want to email all files in a folder, to an email address
(in this case it will be the address in cell A2)

then this will work



Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim StrFile As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "Hi there" & vbNewLine & vbNewLine & _
"See attached" & vbNewLine


On Error Resume Next
With OutMail
.To = Range("a2").Value 'Email Address (can be a cell reference)
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = strbody
StrFile = Dir("C:\*") 'location of files to send
Do While Len(StrFile) > 0
.Attachments.Add ("C:\" & StrFile) 'insert your file path here too no Asterisk *
StrFile = Dir
Loop
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


if you want to open the mails and send them i dont think i will be able to help

[malay]

Hi,

Thank you so much, but sorry to say this solution did not solve my purpose. I need to open each saved ".msg" file and forward it to a certain mail address, reason i want to forward it to keep the mail format and attachment unchanged.

I can read the mail and then take the body content to another mail and then send it to the recipients with attachments if any but after doing this the format get messed. So can you please help me to format the body of the mail content after i put the body content from the saved mails to the new mail body. I am following your code to send mails. So use the same method but provide me solution to format the body of the mail.

Thanks...

[Friel300]

Morning Malay

unfortunately I am not all that informed on this subject, as I only ever needed it to generate new mails. I have never worked with reading them or forwarding them.

For the record I have just tried to send an email, that has attachments & was saved in a folder, as an attachment. it worked fine with the attachments and formatting.

[Excel Fox]

Here's a code to open msg files from a folder, and send it to a mail id



Sub SendMSGFiles()

Dim objItemMSG As Object
Dim strFolderLocation As String
Dim objFolder As Object
Dim strFile As String
Dim olApp As Object

On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
Err.Clear: On Error GoTo 0: On Error GoTo -1
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
End If
Set objFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Select MSG folder location", 0, "")
If Not objFolder Is Nothing Then
strFolderLocation = objFolder.self.Path
Else
Exit Sub
End If
strFile = Dir(strFolderLocation & "\*.msg")
While strFile <> ""
Set objItemMSG = olApp.CreateItemFromTemplate(strFolderLocation & "\" & strFile)
objItemMSG.To = "abc@xyz.com"
objItemMSG.Send
strFile = Dir
Wend

End Sub

[snb]

Simply use sendmail....

[malay]

Hi,

Thank you very much for the support. I could not able to forward the mail but succeed to read the content and paste in another mail and then send to the mail id. I manage to find this code from Ms office help but can't execute it successfully, showing an error message "Run time error 438 Object doesn't support this property or method" at line "Set myinspector = Application.ActiveInspector" in MS office 2007. Please help me to solve this problem and I think my purpose will be solved manipulating the below formula.

Thanks.


Sub RemoveAttachmentBeforeForwarding()
Dim myinspector As Outlook.Inspector
Dim myItem As Outlook.MailItem
Dim myattachments As Outlook.Attachments

Set myinspector = Application.ActiveInspector
If Not TypeName(myinspector) = "Nothing" Then
Set myItem = myinspector.CurrentItem.Forward
Set myattachments = myItem.Attachments
While myattachments.Count > 0
myattachments.Remove 1
Wend
myItem.Display
myItem.Recipients.Add "Dan Wilson"
myItem.Send
Else
MsgBox "There is no active inspector."
End If
End Sub

[Friel300]

I got ExcelFox's to work bang on.

if your files are in a standard location you could change the


Set objFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Select MSG folder location", 0, "")
If Not objFolder Is Nothing Then
strFolderLocation = objFolder.self.Path

to


strFolderLocation ="c:\YOUR LOCATION"

[Excel Fox]

malay, the code you've posted is to remove attachments from the mail. Your question though was regarding opening msg files from a folder, and forwarding it as it is. Am I mistaken?

[malay]

Sorry for late response. Yes, the code attached is to remove attachments but in fact i need item attached to the mail. I just gave you the example from the Ms office help on how to forward mail. Can you do me a favor by writing the complete code to open and forward saved mails with attachments as discussed earlier because i am confused how to write as you found a solution that i cannot understand.:confused:

[malay]

Hi.

The code you provided can open the ".msg" file in forward mode but "From.." option in outlook is showing the person's name from whom the mail has been originally received. This is why the mail cannot be forwarded clicking the send button. Please tell me how to change the original "From.." to the current sender's mail id...:confused:


Sub SendMSGFiles()

Dim objItemMSG As Object
Dim strFolderLocation As String
Dim objFolder As Object
Dim strFile As String
Dim olApp As Object

On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
Err.Clear: On Error GoTo 0: On Error GoTo -1
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
End If
Set objFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Select MSG folder location", 0, "")
If Not objFolder Is Nothing Then
strFolderLocation = objFolder.self.Path
Else
Exit Sub
End If
strFile = Dir(strFolderLocation & "\*.msg")
While strFile <> ""
Set objItemMSG = olApp.CreateItemFromTemplate(strFolderLocation & "\" & strFile)
objItemMSG.To = "abc@xyz.com"
objItemMSG.Send
strFile = Dir
Wend

End Sub

[Excel Fox]

malay, there is a clear instruction on top of the quick reply box to use code tags for any code used in the post. Please adhere to forum rules and guidelines.

So you want to make the mail as if you were sending the mail, instead of it look like a forward mail?